linq-to-sql SelectMany() troubles

Question

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.

evanmcdonnal · Accepted Answer · 2013-05-23 18:03:28Z

1

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.

edited May 23, 2013 at 18:03

answered May 23, 2013 at 17:58

evanmcdonnal

48.5k17 gold badges111 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MacSalty Over a year ago

This makes a lot of sense. My SQL skills are novice at best. I do run into the issue of my return type though. This is stored in a function which has the return type of public static List<document_library> and when I use the .ToList() in the final select statement, I end up with an error. I'm trying to implicitly convert type 'System.Collections.Generic.List<AnnonymousType#4>' to 'System.Collections.Generic.List<~.~.document_library>'. I was trying to figure out the list type for my return type, but I was incorrect in using string

evanmcdonnal Over a year ago

@Anonymity if make it var docs = LINQ result it will work. Or, another option is to define a type and change the new in select to be new ObjectType { \\assignment here } or you could also make a constructor to take those two fields so it would just be new ObjectType(name, size). As you can see the C# compiler is generating a definition for your anonymous type so you can't try to assign that to some other type you've defined or it will cause the above error.

MacSalty Over a year ago

Awesome, thank you @evanmcdonnal. Now I just have to figure out the error it generates and I should be all set. System.Data.SqlClient.SqlException: Invalid column name 'Filename'. Invalid column name 'Filename'. is the error for the record. I stored the information into a constructor in my ViewModel.

evanmcdonnal Over a year ago

@Anonymity that error is from the sql query being executed (LINQ to SQL queries are translated into a normal sql queries then executed by the normal SqlClient). It's trying to select a column named Filename and no such column exists. There's most likely some discrepancy between the column name in the database and the column name your model is generating. Look at the code where that mapping is specified, if you're using EF refreshing your model might do the trick. Good luck.

MacSalty Over a year ago

Thank you. I managed to track down the issue. It's with .GroupBy(item => item.Filename) .Select(group => group.OrderByDescending(p => p.Version).First()). What I've done in the meantime is add in the following

.Where(item => item.Version == db.document_libraries .Where(p => p.Filename == item.Filename) .Select(p => p.Version).Max())

which works. I'll be looking into making the GroupBy work though.

Magnus · Accepted Answer · 2013-05-23 18:07:23Z

Cant you just append a select to you first statement?

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

Collectives™ on Stack Overflow

linq-to-sql SelectMany() troubles

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related