CODEWITHSUNDEEP
c#linqlinq-to-sql
MacSalty
MacSalty

Reputation: 1242

linq-to-sql SelectMany() troubles

I'm trying to find a more elegant way of pulling information from my db to my web application. Currently I pull all data in my table and use only two columns' data. It was suggested that I look into using SelectMany() to accomplish this by being able to select only the columns I need.

I'm not entirely sure how to translate the msdn example to a linq statement using a linq-to-sql db.

My current statement is this:

return db.document_library_sitefiles
         .Where(item => item.SiteID == siteId)
         .Select(item => item.document_library)
         .GroupBy(item => item.Filename)
         .Select(group => group.OrderByDescending(p=>p.Version).First())
         .Where(item => !item.Filename.Contains("*")).ToList();

My current attempt, which I know is wrong, looks like this:

return db.document_library_sitefiles
         .Where(item => item.SiteID == siteId)
         .SelectMany(item => item.document_library, (filename, filesize) 
             => new { filename, filesize })
         .Select(item => new { filename = item.document_library.filename, 
                               filesize = item.document_library.filesize })
         .ToList();

Am I remotely close to getting my intended results?

Basically I want to get the data in my filename and filesize columns without pulling the rest of the data which includes file content (not my design or idea) so I'm not flooding my server with needless information just to show a simple data table of the files currently in this db.

Upvotes: 0

Views: 682

Answers (2)

Magnus
Magnus

Reputation: 46909

Cant you just append a select to you first statement?

....Where(item => !item.Filename.Contains("*"))
    .Select(item => new { 
                            item.Filename, 
                            item.Filesize 
                        }).ToList();

Upvotes: 1

evanmcdonnal
evanmcdonnal

Reputation: 48076

I think you're going in the right direction. It looks like you're just changing the second query in an undesirable way. Give this a try;

return db.document_library_sitefiles
         .Where(item => item.SiteID == siteId)
         .Select(item => item.document_library)
         .GroupBy(item => item.Filename)
         .Select(group => group.OrderByDescending(p=>p.Version).First())
         .Where(item => !item.Filename.Contains("*"))
         .Select( item => new { filename = item.document_library.filename, 
                               filesize = item.document_library.filesize } ).ToList();

Basically you want to keep all of the logic exactly the same as in the first query then just tack on one more select where you initialize the anonymous object to return.

In your attempt at the query you altered some of the underlying logic. You want all of the early operations to remain exactly the same (otherwise the results you return will be from a different set), you only want to transform objects in the resulting set which is why you add a select after the final where.

Upvotes: 1

Related Questions