asp.net mvc 4 -如何选择每个id的第一行

Question

所以我有几个表，我想内部连接它的信息两个创建新对象。但是我有一点小麻烦。

如果我的表有一对多的连接，当linq请求时给我的结果比我想要的多，他只是复制信息。我需要请求如下内容:

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()我需要这样的东西，但我不知道怎么做。我需要你们的帮助。

Answer 1

UPDATE:

回应你的评论，你至少有两个选择:1;使用group by，并从每个组中只选择第一个PhotoUrl，或2。不连接gp_listing_photo表，以避免重复行，并使用子查询只获得第一个PhotoUrl。后者的示例:

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); 

如何简单地在LINQ查询后附加.Distinct()以避免重复数据:

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); 

参考:LINQ Select Distinct with Anonymous type