LINQ QUERY BY Relational division

问题描述

我有2张桌子：



tblCustomers：

I have 2 below tables:

tblCustomers:

id    customerName 
1     John 
2     Philip 
3     Steve

tblOredrs：

tblOredrs:

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

我的条件是：



其中（orderId = 101和orderId = 102）



我想看到所有支付真实订单，对于有订单的客户（101& 102）。但是orderid可能超过2，它必须是动态的。



i必须得到：



john 100

约翰101

菲利普101

菲利普102

my condition is:

where (orderId = 101 and orderId = 102)

I want to see all payment true order, for the customers that have orders (101 & 102). but orderids may be more than 2, it must be dynamic.

i must get:

john 100
john 101
Philip 101
Philip 102

推荐答案

static void Main(string[] args)
      {
          DataTable customer = new DataTable();

          customer.Columns.Add("CustomerId");
          customer.Columns.Add("CustomerName");

          customer.Rows.Add("1", "John");
          customer.Rows.Add("2", "Philip");
          customer.Rows.Add("3", "Steve");

          DataTable orders = new DataTable();

          orders.Columns.Add("Id");
          orders.Columns.Add("CustomerId");
          orders.Columns.Add("OrderId");
          orders.Columns.Add("Payment");

          orders.Rows.Add("1", "1", "100", "True");
          orders.Rows.Add("2", "1", "101", "True");
          orders.Rows.Add("3", "1", "102", "False");
          orders.Rows.Add("4", "2", "101", "True");
          orders.Rows.Add("5", "2", "102", "True");
          orders.Rows.Add("6", "2", "103", "False");
          orders.Rows.Add("7", "3", "101", "True");


//Linq Query What you are expecting

          var results = from myOrders in orders.AsEnumerable()
                        join myCustomer in customer.AsEnumerable()
                        on myOrders.Field<string>("CustomerId") equals myCustomer.Field<string>("CustomerId")

                        where (myOrders.Field<string>("OrderId") == "101"
                            || myOrders.Field<string>("OrderId") == "102"
                            && myOrders.Field<string>("Payment") == "True")

                        select new
                        {
                            Name = myCustomer.Field<string>("CustomerName"),
                            OrderID = myOrders.Field<string>("OrderId")

                        };

          Console.WriteLine("Name" + "\t" + "OrderID");
          foreach (var data in results)
          {
              Console.WriteLine(data.Name + "\t" + data.OrderID);
          }

      }

Output:
Name        OrderID
John            101
Philip          101
Philip          102
Steve           101
Press any key to continue . . .

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; }
   }
   class CustomerOrder
   {
       public static void Main()
       {
           ProcessCustomerOrder();
       }
       public static void ProcessCustomerOrder()
       {
           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
               };
       }
   }

这篇关于LINQ QUERY BY Relational division的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！