CODEWITHSUNDEEP
c#sqllinq
Chris
Chris

Reputation: 6325

How to make this simple SQL query into a Linq Statement?

I have the following SQL statement which is going through a table and selecting out a group of results based on the the data definition. From that group, I get the single result with the highest revision for that group. Now the SQL has been easy, but translating this into Linq so far has yielded nothing which I can use. Can anyone help with this?

select * from datasheet ds where ds.revisionnum = 
(select max(revisionnum) from datasheet where datadefinitionid = 34) 
       and ds.isparent = 1  -- is parent will be true for this query

This is about as far as I get with the Linq Statement before I stumbled:

var query = from ds in context.DataSheets 
             where ds.IsParent == true 
             && ds.RevisionNum  --- ?????

Thanks in advance!

Code Update

After fumbling around for a while, this is what we came up with:

var dataSheet = _dataSheetRepository.All.Where(
                            d => d.DataDefinitionID == i.ID &&
                            d.IsParent == true);

var currentDataSheet = (from ds in dataSheet
                        where (ds.RevisionNum == (from s in dataSheet select
                        s.RevisionNum).Max()) select ds).SingleOrDefault();

Using two variables gave us the ability to narrow down which sheet collection we were looking for, and then using the linq query on the second var, we were able to narrow down which actual revision number of that collection we wanted.

Upvotes: 0

Views: 160

Answers (2)

Andrei
Andrei

Reputation: 56716

try something like that

var query = from ds in context.DataSheets 
             where ds.IsParent == true 
             && ds.RevisionNum == context.DataSheets.Where(
                                  ds => ds.DataDefinitionId == 34).Max(
                                  ds => ds.RevisionNum)

subquery could be converted into linq expression as well

Upvotes: 3

Frode Stenstrøm
Frode Stenstrøm

Reputation: 1048

May I recommend the tool http://www.linqpad.net/

It is great for creating and testing linq expressions against sql and Odata web services.

Upvotes: 0

Related Questions