Store procedure select all fields from One table using join

Question

Hello

I am very frustrated from linq to sql when dealing with many to many relationship with the skip extension. It doesn't allow me to use joinned queries. Not sure it is the case for SQL server 2005 but I am currently using SQL Server 2000.

Now I consider to write a store procedure to fetch a table that is matched by two tables e.g. Album_Photo (Album->Album_Photo<-Photo) and Photo table and only want the Photos data so I match the Album's ID with Album_Photo and use that ID to match the photo. In the store procedure I am just fetch all the joinned data. After that in the linq to sql, I create a new Album object.

e.g.

var albums = (from r in result
    where (modifier_id == r.ModifierID || user_id == r.UserID)
    select new Album() { 
        Name = r.Name, 
        UserID = r.UserID, 
        ModifierID = r.ModifierID, 
        ID = r.ID, 
        DateCreated = r.DateCreated, 
        Description = r.Description, 
        Filename = r.Filename 
    }).AsQueryable();

I used the AsQueryable to get the result as a IQueryable rather than IEnumerable. Later I want to do something with the collection, it gives me this error:

System.InvalidOperationException: The query results cannot be enumerated more than once.

Answer 1

It sounds like you have a situation where the query has already executed by the time you are want to filter it later in your code.

Can you do something like...

var albums = (blah blah blah).AsQueryable().Where(filterClause) when you have enough info to process

what happens if you try albums.where(filter) later on in the code? Is this what you are trying?