代码之家  ›  专栏  ›  技术社区  ›  Ege Bayrak

实体框架lambda中具有不同子句的两个连接到同一个表

  •  0
  • Ege Bayrak  · 技术社区  · 8 年前

    我正在尝试将SQL查询转换为lambda表达式。

    SQL查询如下所示:

    select 
        po.OrderID as order_number,
        pc.emailaddress as user_email,
        pc.CustomerID as customer_code,
        pb.DisplayName as bank,
        poab.street1 + ' ' + poab.Street2 as 'billing_address',
        po.createdate as 'created_date',
        'tr' as currency,
        po.TotalPrice - po.TotalPromotionPrice as discount_amount,
        null as 'extra_field',
        pbi.InstalmentCount as installment_count,
        pp.DisplayName as payment_type,
        poas.street1 + ' ' + poas.street2 as 'shipping_address',
        'UPS' as shipping_company,
        pos.Displayname as status,
        po.ShipmentReferenceNo as 'tracking_number'
    from
        PrmOrder po
    inner join 
        prmcustomer pc on po.CustomerID = pc.customerid
    inner join 
        PrmBank pb on po.BankId = pb.BankId
    inner join 
        prmorderaddress poab on po.orderid = poab.orderid and poab.isbillingaddress = 1
    left join 
        PrmBankInstalment pbi on pbi.BankInstalmentID = po.BankInstalmentID
    inner join 
        PrmPaymentType pp on po.PaymentTypeID = pp.PaymentTypeId
    inner join 
        prmorderaddress poas on po.orderid = poas.orderid and poas.isshippingaddress = 1
    inner join 
        prmorderstatus pos on po.orderstatusid = pos.orderstatusid
    

    由于这两个表之间的一对多连接以及我需要显示两行,所以我对同一个表进行了两次内部连接。基本上,OrderID在OrderAddress表上可以有两条记录,一条记录帐单地址,另一条记录发货地址。它在SQL上运行良好,但我无法将其转换为lambda表达式。

    我先尝试过这样的东西;

    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,
                     billing_address = x.PrmOrderAddresses
                                         .Where(c => c.IsBillingAddress == true && c.OrderID == x.OrderID)
                                         .Select(c => c.Street1 + " " + c.Street2).ToString(),
                     created_date = x.OrderDate,
                     currency = "TRY",
                     discount_amount = x.TotalPrice - x.TotalPromotionPrice,
                     installment_count = x.PrmBankInstalment.InstalmentCount == null ? 0 : x.PrmBankInstalment.InstalmentCount,
                     payment_type = x.PrmPaymentType.DisplayName,
                     shipping_address = x.PrmOrderAddresses
                                         .Where(c => c.IsShippingAddress == true & c.OrderID == x.OrderID)
                                         .Select(c => c.Street1 + " " + c.Street2 + " " + c.PrmAddressCity.DisplayName + " " + c.PrmAddressCountry.DisplayName).ToString(),
                     shipping_company = "UPS",
                     status = x.PrmOrderStatu.DisplayName,
                     tracking_number = x.ShipmentReferenceNo == null ? "" : x.ShipmentReferenceNo
                 });
    

    这导致了V alues of type 'collection[Edm.String(Nullable=True,DefaultValue=,MaxLength=,Unicode=,FixedLength=)]' can not be converted to string. 正如建议的那样,错误和在Edmx中删除/重新添加表不起作用。

    所以现在我尝试用Lambda表达式进行三重连接,感觉不太对劲,看起来有点混乱

    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)
                    .Join(db.PrmOrderAddresses.Where(a => a.IsBillingAddress == true), x => x.OrderID, a => a.OrderID, (x, a) => new
                    {
                        BillingAddress = a.Street1 + " " + a.Street2 + " " + a.PrmAddressCity.DisplayName + " " + a.PrmAddressCountry.DisplayName,
                        OrderID = x.OrderID
                    })
                    .Join(db.PrmOrderAddresses.Where(a => a.IsShippingAddress == true), x => x.OrderID, s => s.OrderID, (x, s) => new
                    {
                        order_number = x.OrderID,
                        user_email = x.PrmCustomer.EmailAddress,
                        customer_code = x.PrmCustomer.CustomerID,
                        order_amount = x.GrandTotal,
                        bank = x.PrmBank.DisplayName,
                        //billing_address = x.PrmOrderAddresses
                        //                    .Where(c => c.IsBillingAddress == true && c.OrderID == x.OrderID)
                        //                    .Select(c => c.Street1 + " " + c.Street2).ToString(),
                        created_date = x.OrderDate,
                        currency = "TRY",
                        discount_amount = x.TotalPrice - x.TotalPromotionPrice,
                        installment_count = x.PrmBankInstalment.InstalmentCount == null ? 0 : x.PrmBankInstalment.InstalmentCount,
                        payment_type = x.PrmPaymentType.DisplayName,
                        //shipping_address = x.PrmOrderAddresses
                        //                    .Where(c => c.IsShippingAddress == true & c.OrderID == x.OrderID)
                        //                    .Select(c => c.Street1 + " " + c.Street2 + " " + c.PrmAddressCity.DisplayName + " " + c.PrmAddressCountry.DisplayName).ToString(),
                        shipping_company = "UPS",
                        status = x.PrmOrderStatu.DisplayName,
                        tracking_number = x.ShipmentReferenceNo == null ? "" : x.ShipmentReferenceNo
                        ShippingAddress = s.Street1 + " " + s.Street2 + " " + s.PrmAddressCity.DisplayName + " " + s.PrmAddressCountry.DisplayName
                    });
    

    我怎样才能实现我的目标?

    1 回复  |  直到 8 年前
        1
  •  2
  •   Camilo Terevinto Chase R Lewis    8 年前

    错误消息告诉您,您无法将表达式转换为字符串,这没有任何意义。
    您必须在使用 ToList / ToArray 如果有很多项目, First 如果始终有一个项目或 FirstOrDefault 如果可能没有:

    var OrderHeader = db.PrmOrders
        .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,
             billing_address = x.PrmOrderAddresses
                 .Where(c => c.IsBillingAddress && c.OrderID == x.OrderID)
                 .Select(c => c.Street1 + " " + c.Street2)
                 .ToList() / .First() / .FirstOrDefault(),
             created_date = x.OrderDate,
             currency = "TRY",
             discount_amount = x.TotalPrice - x.TotalPromotionPrice,
             installment_count = x.PrmBankInstalment.InstalmentCount == null ? 0 : x.PrmBankInstalment.InstalmentCount,
             payment_type = x.PrmPaymentType.DisplayName,
             shipping_address = x.PrmOrderAddresses
                 .Where(c => c.IsShippingAddress && c.OrderID == x.OrderID)
                 .Select(c => c.Street1 + " " + c.Street2 + " " + c.PrmAddressCity.DisplayName + " " + c.PrmAddressCountry.DisplayName)
                 .ToList() / .First() / .FirstOrDefault(),
             shipping_company = "UPS",
             status = x.PrmOrderStatu.DisplayName,
             tracking_number = x.ShipmentReferenceNo == null ? "" : x.ShipmentReferenceNo
         });