LINQ EF QUERY（查看与条件与动态的区别） [英] LINQ EF QUERY (view difference from condition & dynamic)

问题描述

我需要查询过滤记录，得到不同的记录，通过差异条件获取这些记录信息。另外我需要这些是动态的（数量过滤器在第一选择）

I need to make a query to filter records, when get distinct records, get these records information by difference conditions. Also I need these to be dynamic(quantity filter in first select)

让我举个例子：

我有2个表：

tblCustomers：

id    customerName  
1        John  
2        Philip  
3        Steve

tblOrders

tblOrders

id    customerId     ordId    payment
1      1              100      True
2      1              101      True
3      1              102      False 
4      2              101      True
5      2              102      True
6      2              103      False 
7      3              101      True  

我的条件是：

where (orderId = 101 and orderId = 102) 

但是获取该客户的所有记录 payment = true 我的意思是我的情况与什么不同我需要看到。

but get all records of this customer that payment = true I mean my condition is different from what I need to see.

我想以 payment = True 收到所有记录，而不用考虑orderId

I want to receive all records with payment=True without care of orderId

我必须得到：

john    100
john    101  
Philip  101
Philip  102    

结算：我需要两步 - 先过滤客户有orderId = 101& 102，在第二步中，我想显示这些所选择的客户的orderId哪个付款是真的。所以例如在第一步我得到约翰（谁的订单id = 101& 102），然后显示约翰100 - 约翰101（哪个付款是真实的）。考虑tblorder.id = 1不是在第一次查询，但我必须显示在最终结果。

Clearing: I need two step - first filter customer who has orderId=101&102, in second step i want to show these selected customers' orderId which payment is true. so for example in first step i get john(who has order id =101&102) then show john 100 - john 101 (which payment istrue). consider tblorder.id=1 isn't in first query but I must show in final result.

@拉斐尔指示我更好的表达：我想看到所有付款真实订购有订单的客户（101& 102）。但是orderid可能超过2（感谢@Raphael）。

@Raphael direct me to better expression:I want to see all payment true order for the customers that have orders (101 & 102). but orderids may be more than 2 (thanks @Raphael).

第二个问题是：它必须是动态的。有时我有超过10个orderId，必须检查 - 有时较少。我的意思是我的查询必须是灵活的。

2nd problem is: it must be dynamic. Sometimes I have more than 10 orderId that must be checked - sometimes less. I mean my query must be flexible.

在SQL Server select命令中，我可以准备一个字符串变量并使用，但是在linq中我不能这样做。

In SQL Server select command, I can prepare a string variable and use but in linq I can't do it.

推荐答案

void Main()
{
    List<Customer> customers = new List<Customer>
    {
        new Customer { Id = 1, Name = "John" },
        new Customer { Id = 2, Name = "Philip" },
        new Customer { Id = 3, Name = "Steve" }
    };

    List<Order> orders = new List<Order>
    {
        new Order { Id = 1, CustomerId = 1, OrderId = 100, Payment = true },
        new Order { Id = 2, CustomerId = 1, OrderId = 101, Payment = true },
        new Order { Id = 3, CustomerId = 1, OrderId = 102, Payment = false },
        new Order { Id = 4, CustomerId = 2, OrderId = 101, Payment = true },
        new Order { Id = 5, CustomerId = 2, OrderId = 102, Payment = true },
        new Order { Id = 6, CustomerId = 2, OrderId = 103, Payment = false },
        new Order { Id = 7, CustomerId = 3, OrderId = 101, Payment = true }
    };

    List<int> orderIds = new List<int> { 101, 102 };

    var customersWithRelevantOrders =
        from ord in orders
        group ord by ord.CustomerId into customerOrders
        where orderIds.All (
            i => customerOrders.Select (co => co.OrderId).Contains(i))
        select customerOrders.Key;

    var paymentTrueOrdersForTheseCustomers =
        from ord in orders
        join cust in customers on ord.CustomerId equals cust.Id
        where ord.Payment
        where customersWithRelevantOrders.Contains(cust.Id)
        select new
        {
            Name = cust.Name,
            OrderId = ord.OrderId
        };
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int OrderId { get; set; }
    public bool Payment { get; set; }
}

这篇关于LINQ EF QUERY（查看与条件与动态的区别）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！