Linq多个连接-结果太多

本文关键字：结果 太多 连接 Linq | 更新日期: 2023-09-27 18:22:11

我正在努力将sql语句转换为Linq。

我得到的结果太多了，无法确定正确的语法应该是什么。

这是SQL:

SELECT        
    Client_Details_Legacy.FullName, 
    Client_Details_Legacy.AddressLine1, 
    Client_Details_Legacy.AddressLine2, 
    Client_Details_Legacy.AddressLine3, 
    Policy_Property.PolicyNumber, 
    Policy_Property.CoverTo, 
    Clients.Id
FROM            
    Clients 
    INNER JOIN Policy_Property 
        ON Clients.Id = Policy_Property.Client_Id 
    INNER JOIN Client_Details_Legacy 
        ON Clients.Client_Details_Legacy_Id = Client_Details_Legacy.Id
WHERE        
    (Policy_Property.CoverTo >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) 
    AND (Policy_Property.CoverTo <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
ORDER BY 
    Clients.Id

这是我用Linq语句得到的最接近的结果，但它返回的行比它应该返回的行多

我怀疑这与联接有关，但我似乎无法获得正确的语法：

var query = from clients in db.Clients
                        .Include("Client_Details_Legacy")
                        .Include("Policies_Property")
            where clients.Client_Details_Legacy.Country.Code == countryCode 
                  && clients.Policies_Property.Any(x => x.CoverTo >= CoverToStart 
                                                        && x.CoverTo <= CoverToEnd)
            select clients;
return query.ToList();

这是我的模型。。。

public class Client 
{
    public int Id { get; set; }
    [Index]
    [MaxLength(50)]
    public string ClientNumber_Legacy { get; set; }
    [Index]
    [MaxLength(50)]
    public string ClientNuber_Websure { get; set; }
    public Client_Details_Enhanced Client_Details_Enhanced { get; set; }
    public Client_Details_Legacy Client_Details_Legacy { get; set; }
    public Client_Details_Websure Client_Details_Websure { get; set; }
    public List<Policy_Motor> Policies_Motors { get; set; }
    public List<Policy_Property> Policies_Property { get; set; }
}
public class Policy_Property 
{
    [Key]
    [MaxLength(50)]
    public string PolicyNumber { get; set; }
    public Policy_Property_Details_Legacy Policy_Property_Details_Legacy { get; set; }
    public Policy_Property_Details_Enhanced Policy_Property_Details_Enhanced { get; set; }
    public DateTime CoverFrom { get; set; }
    public DateTime CoverTo { get; set; }
    public List<Insured_Property> Insured_Properties { get; set; }
}
public class Client_Details_Legacy 
{
    public int Id { get; set; }
    public ws_lookup_ClientType ClientType { get; set; }
    [DisplayName("Title")]
    public ws_lookup_Title Title { get; set; }
    [Index]
    [DisplayName("Full Name")]
    [MaxLength(250)]
    public string FullName { get; set; }
    public string NationalIdNumber { get; set; }
    public string EmailAddress { get; set; }
    [DisplayName("Address Line 1")]
    [MaxLength(250)]
    public string AddressLine1 { get; set; }
    [DisplayName("Address Line 2")]
    [MaxLength(250)]
    public string AddressLine2 { get; set; }
    [DisplayName("Address Line 3")]
    [MaxLength(250)]
    public string AddressLine3 { get; set; }
    [DisplayName("Address Line 4")]
    [MaxLength(250)]
    public string AddressLine4 { get; set; }
    [DisplayName("Country")]
    public ws_lookup_Country Country { get; set; }
    [DisplayName("Parish")]
    public ws_lookup_Parish Parish { get; set; }
    [DisplayName("Home Telephone Number")]
    [MaxLength(250)]
    public string HomeTelephoneNumber { get; set; }
    [DisplayName("Work Telephone Number")]
    [MaxLength(250)]
    public string WorkTelephoneNumber { get; set; }
    [DisplayFormat(DataFormatString = "{0:dd MMM yyyy}")]
    [DisplayName("Date of Birth")]
    public DateTime? DateOfBirth { get; set; }
    [DisplayName("Gender")]
    public ws_lookup_Gender Gender { get; set; }
    [DisplayName("Loyalty Card")]
    [MaxLength(50)]
    public string LoyaltyCard { get; set; }
    [DisplayName("Occupation")]
    public ws_lookup_Occupation Occupation { get; set; }
}

有人能帮我找出我做错了什么吗？

Linq多个连接-结果太多

问题的一部分是，您的查询返回某些数据，而您正试图返回特定的客户端。但这是行不通的，因为客户端与多个策略相关联，但您只对特定的策略感兴趣。以下内容应该会给出与SQL相同的结果。

var query = from client in db.Clients
            from policy in client.Policies_Property
            where policy.CovertTo >= ConvertToStart && policy.ConverTo <= ConverToEnd
                  && client.Client_Details_Legacy != null
            select new
            {
                client.Client_Details_Legacy.FullName, 
                client.Client_Details_Legacy.AddressLine1, 
                client.Client_Details_Legacy.AddressLine2, 
                client.Client_Details_Legacy.AddressLine3, 
                policy.PolicyNumber, 
                policy.CoverTo, 
                client.Id
            };

注意，我省略了client.Client_Details_Legacy.Country.Code == countryCode，因为它在您的SQL中不存在。

您的SQL语句可以通过以下方式轻松地转换为LINQ：

var query = from clients in db.Clients
                        join cdl in db.Client_Details_Legacy on cdl.Id = clients.Client_Details_Legacy_Id
                        join pp in db.Policies_Property on pp.Client_Id = clients.Id
            where pp.CoverTo >= CoverToStart && pp.CoverTo <= CoverToEnd
            select new { 
                FullName = cdl.FullName, 
                AddressLine1 = cdl.AddressLine1, 
                AddressLine2 = cdl.AddressLine2, 
                AddressLine3 = cdl.AddressLine3, 
                PolicyNumber = pp.PolicyNumber,
                CoverTo = pp.CoverTo,
                Id = clients.Id
                };
return query.OrderBy(q => q.Id).ToList();

我认为这里的行是问题所在：

clients.Policies_Property.Any(x => x.CoverTo >= CoverToStart && x.CoverTo <= CoverToEnd)

如果满足1，则使用Any将返回所有记录，而不是仅返回满足该子句的行。

Enumerable.Any：确定序列的任何元素是否满足条件。

如果没有查看数据或测试的手段，您可以尝试。。。

where clients.Client_Details_Legacy.Country.Code == countryCode 
                 && (clients.Policies_Property.CoverTo >= CoverToStart &&
                    clients.Policies_Property.CoverTo <= CoverToEnd)
相关文章：