All geek questions in one place

Linq query with multiple subqueries

I am working on converting an Oracle Sql query to Linq and do not know how to proceed. Here is the Sql request:

SELECT *
FROM   CustomerShip,
    (SELECT DISTINCT b.ShipSeq AS shipSeq
     FROM   Orders a,
            CustomerShip b
     WHERE  a.OrderId IN (SELECT OrderId
                          FROM   Orders
                          WHERE  CustomerId = @CustomerId
                          AND    OrderType <> 'A')
     AND    b.CustomerId = @CustomerId
     AND    b.ShipSeq = a.CustShip
     AND    OrderStatus <> 'C'
     GROUP BY b.ShipSeq) i
WHERE  CustomerId = @CustomerId
AND    (Address NOT LIKE '%RETAIL%STORE%')
AND    ShipSeq = i.ShipSeq(+)
ORDER BY ShipTo DESC, OrderDate DESC;

I tried splitting it into three separate queries when converting to linq.

var query1 = from c in CustomerShip
            where c.CustomerId == customerId
            && !c.Address.Contains("RETAIL")
            && !c.Address.Contains("STORE")
            orderby c.ShipTo descending, c.OrderDate descending
            select c;

var query2 = from o in Orders
         where o.CustomerId == customerId
         && !o.OrderType.Equals("A")
         select o.OrderId;

var query3 = (from o in Orders
         from c in CustomerShip
         where c.CustomerId == customerId
         && c.ShipSeq == o.CustShip
         && !o.OrderStatus.Equals("A")
         select c.ShipSeq).Distinct();

Now I'm trying to collect them all in one request, but I don’t know how to do it. Here is the direction I'm going in:

var query = from c in CustomerShip

let subquery = from o in Orders
               where o.CustomerId == customerId
               && !o.OrderType.Equals("A")
               select o.OrderId

    from or in model.Orders
    where subquery.Contains(or.OrderId) 
    && c.CustomerId == customerId
    && c.ShipSeq == or.CustShip
    && !or.OrderStatus.Equals("A")
    group c by c.ShipSeq
    into i
    select c.ShipSeq

where c.CustomerId == customerId
&& !c.Address.Contains("RETAIL")
&& !c.Address.Contains("STORE")
orderby c.ShipTo descending, c.OrderDate descending 
select c, i;

UPDATE

I have a query that executes types, but it takes almost two minutes to execute (compared to 0.02 for an Oracle query), and the order of the results is incorrect. Does anyone see what I'm missing?

var innerQuery = from x in model.Orders
                    where x.CustomerId == customerId
                    && !x.OrderType.Equals("A")
                    select x.OrderId;

var result = from c in model.CustomerShip
            join subQuery in 
            (
                (from o in model.Orders
                from c in model.CustomerShip 
                where c.CustomerId == customerId
                && innerQuery.Contains(o.OrderId)
                && !o.FLAG_ORD_STATUS.Equals("C")
                && c.ShipSeq == o.CustShip
                select c.ShipSeq).Distinct()

            ) on c.ShipSeq equals subQuery into temp
            from x in temp.DefaultIfEmpty()
            where c.CustomerId == customerId
            && !c.Address.Contains("RETAIL")
            && !c.Address.Contains("STORE")
            orderby c.ShipTo descending, c.OrderDate descending
            select c;
+4
source share
4 answers

, . , ToList() .FirstOrDefault() - . SO, , SQL.

var query2 = from o in Orders
             where o.CustomerId == customerId
             && !o.OrderType.Equals("A")
             select o.OrderId;

var query3 = (from o in Orders
              join c in CustomerShip on o.CustShip equals c.ShipSeq 
              where c.CustomerId == customerId
              && !o.OrderStatus.Equals("A")
              && query2.Contains(o.OrderId)
              select c.ShipSeq).Distinct();

var query1 = from c in CustomerShip
             from i in query3
             where c.CustomerId == customerId
             && !c.Address.Contains("RETAIL")
             && !c.Address.Contains("STORE")
             && c.ShipSeq == i.ShipSeq
             orderby c.ShipTo descending, c.OrderDate descending
             select c;

, query2 query3 :

var query3 = (from o in Orders
              join c in CustomerShip on o.CustShip equals c.ShipSeq 
              where c.CustomerId == customerId
              && !o.OrderStatus.Equals("A")
              && !o.OrderType.Equals("A")
              select c.ShipSeq).Distinct();
+5

- . . ShipSeq, . , .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace ConsoleApplication60
{
    class Program
    {
        static void Main(string[] args)
        {
            int customerID = 1234;
            List<Order> CustomTypeA = Order.orders
                .Where(x => (x.CustomerId == customerID) && (x.OrderType == "A") && (x.OrderStatus == "C")).ToList();

            var results = (from CustA in CustomTypeA 
                          join CustShip in Ship.CustomerShip on CustA.CustomerId equals CustShip.CustomerId 
                          select new { CustA = CustA, CustShip = CustShip})
                          .Where(x => (!RetailStore(x.CustShip.Address)) && (x.CustA.CustShip == x.CustShip.ShipSeq))
                          .OrderByDescending(x => x.CustShip.OrderDate)
                          .GroupBy(x => x.CustShip.ShipSeq)
                          .Select(x => x.FirstOrDefault())
                          .Select(x => new {
                              CustomerID = x.CustShip.CustomerId,
                              Address = x.CustShip.Address,
                              OrderDate = x.CustShip.OrderDate
                          }).ToList();

        }
        static Boolean RetailStore(string address)
        {
            string pattern = "RETAIL.*STORE";
            return Regex.IsMatch(address, pattern);
        }
    }
    public class Order
    {
        public static List<Order> orders = new List<Order>();

        public int CustomerId { get; set; }
        public string OrderType { get; set; }
        public string CustShip { get; set; }
        public string OrderStatus { get; set; } 
    }
    public class Ship
    {
        public static List<Ship> CustomerShip = new List<Ship>();

        public int CustomerId { get; set; }
        public string ShipSeq { get; set; }
        public string Address { get; set; }
        public DateTime OrderDate { get; set; }
     }
}
+1

query2 query3

var Innerquery = (from o in Orders
              join c in CustomerShip on o.CustShip equals c.ShipSeq 
              where c.CustomerId == customerId
              && !o.OrderStatus.Equals("A")
              && !o.OrderType.Equals("A")
              select c.ShipSeq).Distinct();

var query1 = from c in CustomerShip
             from i in query3
             where c.CustomerId == customerId
             && innerquery.Contains(c.CustomerId)
             && !c.Address.Contains("RETAIL")
             && !c.Address.Contains("STORE")
             && c.ShipSeq == i.ShipSeq
             orderby c.ShipTo descending, c.OrderDate descending
             select c;

Linqer http://www.sqltolinq.com

0

EF , - .

: EF ( , EF Oracle) , , , .

, , -, .NET, View, dbcontext.Database.SqlQuery<CustomerShip>(sql) , , Oracle, , ( - , .)

0

Source: https://habr.com/ru/post/1678792/

More articles:


All Articles