SQL to Linq - selecting first matching rows

Question

Ive been struggling to find an answer to this task problem I am solving, despite spending the morning no here searching. I have looked into the MIN/MAX clauses but cannot seem to fit it in my scenario properly.

I am performing this simple LINQ query where i search all rows where the column matches

using (DataClasses2DataContext db = new DataClasses2DataContext())
        {
            var routes = (

                          from txcalllines in db.TxcAllLines
                          where
                              txcalllines.LineName == searchString
                          select txcalllines);

            return routes.ToList();
        }

And this returns these results:

FILE                           LINE   START               FINISH
output_txc_45486m.xml           486   North Station       Friswell Place
SVRAYAO486-20121008-22264.xml   486   Dunoon              Inveraray
SVRAYAO486-20121008-22265.xml   486   Dunoon              Inveraray
SVRAYAO486-20121008-22266.xml   486   Dunoon              Inveraray
SVRGMB04860-20120103-5774.xml   486   BURY                RADCLIFFE
SVRGMB04860-20120103-5775.xml   486   BURY                RADCLIFFE
SVRYNAO486-20120217-44588.xml   486   Selby Bus Stn       Pollington Circular

The problem is, running this query returns several rows of the same route (you can see LINE, START, and FINISH are the same), where I only want to return the first matching row of each route.

So the desired result would be:

FILE                           LINE   START               FINISH
output_txc_45486m.xml           486   North Station       Friswell Place
SVRAYAO486-20121008-22264.xml   486   Dunoon              Inveraray
SVRGMB04860-20120103-5774.xml   486   BURY                RADCLIFFE
SVRYNAO486-20120217-44588.xml   486   Selby Bus Stn       Pollington Circular

It just puts it in a list some my client application can read it nicely. (The above code is from a WCF service) — Dan Sewell, Feb 02 '13 at 13:40

Cédric Bignon · Accepted Answer · 2013-02-02T14:25:42.120

1

You can use GroupBy then get the first element of each group.

var routes = (from txcalllines in db.TxcAllLines
              where txcalllines.LineName == searchString
              group txcalllines by new { txcalllines.Start, txcalllines.Finish } into g  // Groups the txcalllines by the pair (Start, Finish)
              select g.First());  // Gets the first intem of the group

edited Feb 02 '13 at 14:25

answered Feb 02 '13 at 13:44

Cédric Bignon

12,892
3
39
51

Hi Cedric, thanks for your answer, but it does not compile. It complains of several issues, including "invalid expression term 'in'", and expecting a few ) and ; – Dan Sewell Feb 02 '13 at 14:20
1

@DanSewell I have update my post (just changed _in_ into _into). – Cédric Bignon Feb 02 '13 at 14:26

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

I misunderstood the question, I apologize... Maybe this could be to help? https://stackoverflow.com/a/706903/1380061

edited May 23 '17 at 11:56

Community

1
1

answered Feb 02 '13 at 13:46

Fredrik

2,247
18
21

Sorry I am not sure you understand, I want to return several rows (edited question to make this clearer) – Dan Sewell Feb 02 '13 at 13:51

SQL to Linq - selecting first matching rows

2 Answers2