C# writing an linq group by and having statement

Question

0.00/5 (No votes)

See more:

I need to write the sql statement:

SELECT        memberid, spid, Score
FROM            Mem_Gifts AS e
WHERE        (Score =
                             (SELECT        MAX(Score) AS Expr1
                               FROM            Mem AS w
                               GROUP BY memberid
                               HAVING         (e.memberid = memberid)))

to linq. I'm having trouble with the Having. Adding the where at the end gives me an error. The type or name space 'where' could not be found.

What I have tried:

var maxScore2 = (from c in storeDB.Mem
                                         group c by new { c.memberid, c.spid, c.Score } into g
                                         select new
                                         {
                                             g.Key,
                                             Score = (from t2 in g select t2.Score).Max()
                                         }) where c.memberid = g.memberid;

Posted 5-Mar-20 6:02am

9999Dave

Updated 5-Mar-20 13:31pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2020-03-05T07:04:00

Solution 1

Start by simplifying your SQL query:

SQL

SELECT 
    memberid, 
    spid, 
    score
FROM 
    Mem_Gifts As e
WHERE 
    score = 
    (
        SELECT MAX(w.score) 
        FROM Mem As w 
        WHERE w.memberid = e.memberid
    )
;

No need for the GROUP BY or HAVING clauses, since you're using a correlated sub-query.

To get a similar result in LINQ, try something like this:

C#

var result = storeDB.MemGifts
    .Where(e => e.Score == storeDB.Mem.Where(w => w.MemberId == e.MemberId).Max(w => w.Score))
    .Select(e => new { e.MemberId, e.Spid, e.Score });

Posted 5-Mar-20 7:04am

Richard Deeming

Comments

Maciej Los 5-Mar-20 13:50pm

5ed!

9999Dave 5-Mar-20 16:25pm

That worked. My only comment is the Mem_Gifts in mine and the example is supposed to be Mem. Typeo by me. It all worked fine!

Richard Deeming 6-Mar-20 6:29am

If it's the same table, then you should be able to do:

var result = storeDB.Mem
    .GroupBy(e => e.MemberId, (key, list) => list.OrderByDescending(e => e.Score).First())
    .Select(e => new { e.MemberId, e.Spid, e.Score });

Or, if Entity framework can't convert that to a SQL query, then:

var result = storeDB.Mem
    .GroupBy(e => e.MemberId)
    .Select(group => group.OrderByDescending(e => e.Score).First())
    .Select(e => new { e.MemberId, e.Spid, e.Score });

9999Dave 6-Mar-20 7:24am

I just changed the storeDB.MemGifts to storeDB.Mem in the first answer, and it worked fine.