Entity Framework Lambda中如何实现对同一表的两次不同条件连接？

首先，咱们先搞清楚你第一次尝试出错的核心原因：你在Select里用x.PrmOrderAddresses.Where(...).Select(...).ToString()的时候，Where返回的是集合类型（哪怕实际只有一条数据），直接调用ToString()只会输出集合的类型名称，而且EF无法把集合转换成字符串类型，这就是你看到那个错误的根源。

因为你的SQL里是内连接地址表，意味着每个订单肯定有对应的账单和收货地址，所以咱们只需要获取集合里的单个地址对象，再拼接字符串就可以了。下面给你两种靠谱的实现方式：

方式一：使用Include + 单个地址获取（更直观易读） #

这种方式保留了你最初的Include思路，只需要修正地址拼接的部分，用FirstOrDefault()获取单个地址（因为一对多关系里每个订单对应一个账单/收货地址）：

var OrderHeader = db.PrmOrders
    .Include(x => x.PrmCustomer)
    .Include(x => x.PrmBank)
    .Include(x => x.PrmOrderAddresses)
    .Include(x => x.PrmBankInstalment)
    .Include(x => x.PrmPaymentType)
    .Include(x => x.PrmOrderStatu)
    .Where(x => x.PrmCustomer.EmailAddress != null && x.OrderStatusID != 1 && x.OrderID > OrderID)
    .OrderBy(x => x.OrderID)
    .Select(x => new HeaderDTO()
    {
        order_number = x.OrderID,
        user_email = x.PrmCustomer.EmailAddress,
        customer_code = x.PrmCustomer.CustomerID,
        order_amount = x.GrandTotal,
        bank = x.PrmBank.DisplayName,
        // 修正：用FirstOrDefault获取单个账单地址，再拼接字符串
        billing_address = x.PrmOrderAddresses
            .FirstOrDefault(c => c.IsBillingAddress == true) is var billingAddr 
                ? $"{billingAddr.Street1} {billingAddr.Street2}".Trim() 
                : string.Empty,
        created_date = x.OrderDate,
        currency = "TRY",
        discount_amount = x.TotalPrice - x.TotalPromotionPrice,
        installment_count = x.PrmBankInstalment?.InstalmentCount ?? 0,
        payment_type = x.PrmPaymentType.DisplayName,
        // 同理修正收货地址
        shipping_address = x.PrmOrderAddresses
            .FirstOrDefault(c => c.IsShippingAddress == true) is var shippingAddr
                ? $"{shippingAddr.Street1} {shippingAddr.Street2} {shippingAddr.PrmAddressCity.DisplayName} {shippingAddr.PrmAddressCountry.DisplayName}".Trim()
                : string.Empty,
        shipping_company = "UPS",
        status = x.PrmOrderStatu.DisplayName,
        tracking_number = x.ShipmentReferenceNo ?? string.Empty
    });

如果你用的是旧版本C#，可以把模式匹配语法换成链式空值调用：

billing_address = $"{x.PrmOrderAddresses.FirstOrDefault(c => c.IsBillingAddress == true)?.Street1} {x.PrmOrderAddresses.FirstOrDefault(c => c.IsBillingAddress == true)?.Street2}".Trim()

方式二：使用Join（完全匹配原始SQL逻辑） #

你之前的Join写法出错是因为第一次Join后只保留了BillingAddress和OrderID，丢失了原PrmOrder对象的其他关联属性（比如PrmCustomer）。正确的做法是把原Order对象一起传递到后续的Join中：

var OrderHeader = db.PrmOrders
    .Include(x => x.PrmCustomer)
    .Include(x => x.PrmBank)
    .Include(x => x.PrmBankInstalment)
    .Include(x => x.PrmPaymentType)
    .Include(x => x.PrmOrderStatu)
    .Where(x => x.PrmCustomer.EmailAddress != null && x.OrderStatusID != 1 && x.OrderID > OrderID)
    .OrderBy(x => x.OrderID)
    // 第一次Join账单地址，保留原Order对象
    .Join(
        db.PrmOrderAddresses.Where(a => a.IsBillingAddress == true),
        order => order.OrderID,
        billingAddr => billingAddr.OrderID,
        (order, billingAddr) => new { Order = order, BillingAddr = billingAddr }
    )
    // 第二次Join收货地址，用之前的匿名对象关联
    .Join(
        db.PrmOrderAddresses.Where(a => a.IsShippingAddress == true),
        prev => prev.Order.OrderID,
        shippingAddr => shippingAddr.OrderID,
        (prev, shippingAddr) => new HeaderDTO()
        {
            order_number = prev.Order.OrderID,
            user_email = prev.Order.PrmCustomer.EmailAddress,
            customer_code = prev.Order.PrmCustomer.CustomerID,
            order_amount = prev.Order.GrandTotal,
            bank = prev.Order.PrmBank.DisplayName,
            billing_address = $"{prev.BillingAddr.Street1} {prev.BillingAddr.Street2}".Trim(),
            created_date = prev.Order.OrderDate,
            currency = "TRY",
            discount_amount = prev.Order.TotalPrice - prev.Order.TotalPromotionPrice,
            installment_count = prev.Order.PrmBankInstalment?.InstalmentCount ?? 0,
            payment_type = prev.Order.PrmPaymentType.DisplayName,
            shipping_address = $"{shippingAddr.Street1} {shippingAddr.Street2} {shippingAddr.PrmAddressCity.DisplayName} {shippingAddr.PrmAddressCountry.DisplayName}".Trim(),
            shipping_company = "UPS",
            status = prev.Order.PrmOrderStatu.DisplayName,
            tracking_number = prev.Order.ShipmentReferenceNo ?? string.Empty
        }
    );

这种写法完全对应你原始SQL的内连接逻辑，适合需要严格匹配SQL执行计划的场景。

小总结 #

• 如果地址是必选（内连接），两种方式都可以，第一种更简洁易维护；
• 如果是可选地址（左连接），第一种方式用FirstOrDefault()可以自然处理空值，不需要额外调整Join类型；
• 全程注意空值处理：用??或者模式匹配避免NullReferenceException，同时符合EF的查询转换规则。

内容的提问来源于stack exchange，提问作者Ege Bayrak