Linq with join需要按ID区分行

Question

我得到了多行的组合。我需要唯一的供应商行与唯一的VendorCod。

代码：

IQueryable<VendorModel> vendors = (from vendor in _context.Vendor
                               join vp in _context.VendorProduct on vendor.Pkid equals vp.VendorId
                               where vp.ProductId == ProductId

                               select new VendorModel()
                               {
                                   VendorId = vendor.Pkid,
                                   Name = vendor.Name,
                                   VendorCode = vendor.VendorCode,
                                   FacilityId = 0,
                                   ClientId = 0,
                                   Active = vendor.Active ?? false,
                                   UpdatedAt = vendor.UpdatedAt,
                                   DeletedAt = vendor.DeletedAt
                               });

Answer 1

对于一个供应商，您有许多产品，因此您必须应用适当的谓词，并且不需要连接。

通过导航属性：

var vendors = 
   from vendor in _context.Vendor
   where vendor.Products.Any(vp => vp.ProductId == ProductId)
   select new VendorModel
   {
       VendorId = vendor.Pkid,
       Name = vendor.Name,
       VendorCode = vendor.VendorCode,
       FacilityId = 0,
       ClientId = 0,
       Active = vendor.Active ?? false,
       UpdatedAt = vendor.UpdatedAt,
       DeletedAt = vendor.DeletedAt
   };

或者，如果您没有导航属性：

var vendors = 
   from vendor in _context.Vendor
   where _context.VendorProduct.Any(vp => vendor.Pkid == vp.VendorId && vp.ProductId == ProductId)
   select new VendorModel
   {
       VendorId = vendor.Pkid,
       Name = vendor.Name,
       VendorCode = vendor.VendorCode,
       FacilityId = 0,
       ClientId = 0,
       Active = vendor.Active ?? false,
       UpdatedAt = vendor.UpdatedAt,
       DeletedAt = vendor.DeletedAt
   };

Answer 2

您需要对产品进行分组，并检查是否有与代码匹配的产品

IQueryable<VendorModel> vendors = (from vendor in _context.Vendor
                               join vp in _context.VendorProduct on vendor.Pkid equals vp.VendorId into vpg
                               where vpg.Any(vp => vp.ProductId == ProductId)

                               select new VendorModel()
                               {
                                   VendorId = vendor.Pkid,
                                   Name = vendor.Name,
                                   VendorCode = vendor.VendorCode,
                                   FacilityId = 0,
                                   ClientId = 0,
                                   Active = vendor.Active ?? false,
                                   UpdatedAt = vendor.UpdatedAt,
                                   DeletedAt = vendor.DeletedAt
                               });