Click here to Skip to main content
15,889,877 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to convert the following sql statement to an linq.
SQL
SELECT e.memberid, e.spid, e.Score, m.Class
FROM   Mem AS e INNER JOIN
       Members AS m ON e.memberid = m.membersid
WHERE  (e.Score =
           (SELECT MAX(Score) AS Expr1
            FROM   Mem AS w
            WHERE  (memberid = e.memberid))
       ) 
AND 
       (m.Class = '1st grade')

This is the same as https://www.codeproject.com/Questions/5261381/Csharp-writing-an-linq-group-by-and-having-stateme except I added a join to give the name of the class in the result, and to filter by class.

What I have tried:

I've tried,
C#
var maxScore2 = (from z in storeDB.Mem join mg in storeDB.Members on z.memberid equals mg.memberid)
    .Where(e => e.Score == storeDB.Mem.Where(w => w.memberid == e.memberid).Max(w => w.Score))
    .Where(t => t.sid == (i + 1))
    .Select(e => new { e.memberid, e.spid, e.Score });

and
C#
var mem_con_mem = from h in m_members
                  join mg in storeDB.Mem on h.memberid equals mg.memberid 
                  select h.First_Name;
                                    
maxScore2 = mem_con_mem
    .Where(e => e.Score == storeDB.Mem.Where(w => w.memberid == e.memberid).Max(w => w.Score))
    .Where(t => t.spid == (i + 1))
    .Select(e => new { e.memberid, e.spid, e.Score });

both give errors.
Posted
Updated 7-Mar-20 6:01am
v2
Comments
Richard Deeming 6-Mar-20 8:01am    
What errors?
9999Dave 6-Mar-20 11:36am    
The first on at the end of the second line say, "A query body must end with a select clause or a group clause.

The second one at e.Score gives error, "String does not contain a definition for 'Score' and no accessible extension method 'Score' accepting a first argument of type 'string' could be found." and at e.memberid it gives error, "String does not contain a definition for 'memberid' and no accessible extension method 'memberid' accepting a first argument of type 'string' could be found."

1 solution

This error: A query body must end with a select clause or a group clause
refers to this line:
from z in storeDB.Mem join mg in storeDB.Members on z.memberid equals mg.memberid

You should add select statement at the end of line.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900