Trying to get the first record for each MemID in this LINQ Query

Question

I have a LINQ query, which is working, as below. The only problem is that sometimes I get repeating MEMIds. How can I get only the first MemID from this query in a single database trip? I am using SQL Server 2008 R2 as my backend database, and C# as the programming language.

var query = (from m in e.Memberships
             where m.MEMID != null 
                  && (SqlFunctions.StringConvert((double)m.MEMID).Contains(memIdOrName) 
                        || m.NAME.Contains(memIdOrName))  
             select new { 
                             m.MEMID, 
                             NAME = m.NAME.TrimEnd(), 
                             m.CITY, 
                             m.STATE, 
                             m.SYSTEMID, 
                             SYSTEMNAME = m.SYSTEMNAME.TrimEnd() 
                        })
                        .Distinct()
                        .OrderBy(s => s.NAME)
                        .ThenBy(s => s.CompanyID)
                        .ThenBy(s => s.CITY)
                        .ThenBy(s => s.MEMID);

var a = query.Skip(startRowIndex).Take(maximumRows).ToList();

Servy · Accepted Answer · 2014-06-24T17:18:49.800

1

Group on that value and then select out just one item from that group. If you don't care which, you can just grab the first. If you want a particular one, then you can re-order them before taking the first item.

So replace Distinct with;

//everything before `Distinct`
.GroupBy(s => s.MEMID)
.Select(group => group.FirstOrDefault())//or some other query to get one item in the group
//rest of your query

edited Jun 24 '14 at 17:18

answered Jun 24 '14 at 16:18

Servy

202,030
26
332
449

Ok. I will try and let you know. Thanks. – Sunil Jun 24 '14 at 16:23
Depending on the underlying query provider, it's likely you'll need to use `.FirstOrDefault()` instead of `.First()` – StriplingWarrior Jun 24 '14 at 17:16
This works. BUT the performance is very poor now. Before it was fast and almost instant. – Sunil Jun 24 '14 at 17:53
@Sunil Well, it's doing something radically different. How fast something executes is irrelevant if it doesn't actually *work*. – Servy Jun 24 '14 at 18:15
@Sevy, Yes that's true. One question: is the query doing the grouping on database side or on code-side? – Sunil Jun 24 '14 at 18:33
@Sunil I'm not sure whether the query provider can translate this or not. You're best bet is to look at the log and see for yourself. – Servy Jun 24 '14 at 18:36

Trying to get the first record for each MemID in this LINQ Query

1 Answers1