How to select first row of each id linq

Question

So I have few tables and i want inner join it information two create new object. But I have a little bit trouble.

One my table have connection one to many, and when linq request , it give me more result than i want , he just copy information. I need request something like this:

IPagedList<HelperListings> srch = (from l in db.gp_listing
                                  where l.DateCreated > weekago

                                  join lp in db.gp_listing_photo on l.Id equals lp.ListingId
                                  join loc in db.gp_location on l.LocationId equals loc.Id

                                  orderby l.DateCreated ascending
                                  select new HelperListings { id = l.Id, HouseNumber = l.HouseNumber,ListingPrice = l.ListingPrice, PhotoUrl = lp.PhotoUrl.First(), AreaStateCode = loc.AreaStateCode }).ToList().ToPagedList(page ?? 1, 15);

PhotoUrl = lp.PhotoUrl.First() i need something like this but i don`t have any ideas how to do it. Need ur help guys.

score 0 · Accepted Answer · edited May 23 '17 at 12:31

UPDATE :

Responding to your comment, you have at least 2 options : 1. use group by and select only the first PhotoUrl from each group, or 2. don't join to gp_listing_photo table to avoid duplicated rows, and use subquery to get only the first PhotoUrl. Example for the latter :

IPagedList<HelperListings> srch = 
                    (from l in db.gp_listing
                     where l.DateCreated > weekago
                     join loc in db.gp_location on l.LocationId equals loc.Id
                     orderby l.DateCreated ascending
                     select new HelperListings 
                                { 
                                    id = l.Id, 
                                    HouseNumber = l.HouseNumber,
                                    ListingPrice = l.ListingPrice, 
                                    PhotoUrl = (from lp in db.gp_listing_photo where l.Id = lp.ListingId select lp.PhotoUrl).FirstOrDefault(), 
                                    AreaStateCode = loc.AreaStateCode 
                                }
                    ).ToList().ToPagedList(page ?? 1, 15);

~~How about simply appending .Distinct() after your LINQ query to avoid duplicated data :~~

IPagedList<HelperListings> srch = 
                (from l in db.gp_listing
                 where l.DateCreated > weekago

                 join lp in db.gp_listing_photo on l.Id equals lp.ListingId
                 join loc in db.gp_location on l.LocationId equals loc.Id

                 orderby l.DateCreated ascending
                 select new HelperListings 
                            { 
                                id = l.Id, 
                                HouseNumber = l.HouseNumber,
                                ListingPrice = l.ListingPrice, 
                                PhotoUrl = lp.PhotoUrl, 
                                AreaStateCode = loc.AreaStateCode 
                            }
                ).Distinct().ToList().ToPagedList(page ?? 1, 15);

For Reference : LINQ Select Distinct with Anonymous Types

When I inner join gp_listing_photo , one gp_listing id have few photos ... so he create me 3 lines. But i need to take just first photo. — nick shp, Mar 27 '14 at 15:52
yes, and if I understand the situation correctly, in that case you'll get 3 lines containing same values in the final result of your LINQ query. That's why I suggest to add `.Distinct()`. It will filters out lines containing exactly same values. Does it makes sense? — har07, Mar 28 '14 at 02:59
yes 3 lines containings same values , but one column will be different , PhotoUrl, becausse it contains many photo url on each id and url is different too. so one column will be unique and i can`t use `distinct()`. — nick shp, Mar 28 '14 at 06:44
I think I understood the problem differently. Check my updated answer for possible solution to your *actual* problem. — har07, Mar 28 '14 at 13:22
Oh at last , `lp.gp_listing_photo.Select(x=>x.PhotoUrl).FirstOrDefault() ! Thanks man a lot` :D — nick shp, Mar 28 '14 at 15:29

How to select first row of each id linq

1 Answers1