Linq query with multiple subqueries

Question

I'm working on converting an Oracle Sql query to Linq, and not sure how to proceed. Here is the Sql query:

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 have tried to break it down 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 assemble them all into one query, but unsure how to do it. Here is the direction I am going:

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 kinds works, but the it takes almost two minutes to execute (compared to .02s for the Oracle query) and the order of the results is incorrect. 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;

Read up on the join ability of Linq... You can rewrite this to look very similar to your SQL, don't break it up.... — JWP, Jun 08 '17 at 13:28
I'm working my way through this but I'm confused by this line: `AND ShipSeq = i.ShipSeq(+)`. Is this unique to Oracle? — PixelPaul, Jun 08 '17 at 18:33
@PixelPaul The `(+)` is Oracle's old way of indicating outer joins. Here's it means "All customer records, plus the records from the inner query that match on ShipSeq. Details here: https://stackoverflow.com/q/6559261/12725 — James Curran, Jun 19 '17 at 17:38
This is an answer, so I'll post just a comment: NO. Linq scope is to query .NET objects, not to query a database. Each one its own: put the query into a View on the DB, then interact with it by using EF or whatever ORM you are using. Linq will never ever produce the best query, its execution plan will end up trashed often, the DBE will have to recompile the query, forget any optimisation, and so on. Do not, *ever*, use Linq to query a DB. (and btw you are still lucky, I often have to repair damage done by damaged devs who use linq on DB, and the worst I've seen is 8 minutes against 0.4 secs) — motoDrizzt, Jun 22 '17 at 12:17
Agree with @motoDrizzt, you have to create a stored procedure, and then execute it with EF if you wish. Indeed, stored procedures are already "compiled", optimised and ready for execution. It can seems ugly but this is a better way for achieving that you trying to do. — csblo, Jun 22 '17 at 15:27

score 8 · Answer 1 · answered Jun 08 '17 at 13:59

Remember that you are just build a query here. Nothing is executed until you do a ToList() or .FirstOrDefault() or whatever. SO, you can use the queries in other queries, and it will create one big SQL statement when executed.

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;

However, I'm pretty sure you can reduce query2 and query3 down to just:

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();

jdweng · Answer 2 · 2017-06-08T14:35:56.017

Try something like this. I model some classes just for getting the error out. If you group by ShipSeq you don't need distinct. Just take first item from group will give same results.

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; }
     }
}

score 1 · Answer 3 · edited Jun 20 '17 at 16:18

query2 and query3 Merged here into inner query

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;

OR you can try Linqer http://www.sqltolinq.com

score 0 · Answer 4 · answered Jun 21 '17 at 16:23

There could be many reasons why your query is slow in EF - I would suggest using a profiler.

The probable reasons are either EF creates an inefficient query (usually the database should create its own optimizations, but I have had bad experiences with EF and Oracle), or, depending on how many results it loads, mapping it to actual objects is very expensive.

In general, although seemingly not a popular opinion in the .NET world, I would suggest either creating a View, or using dbcontext.Database.SqlQuery<CustomerShip>(sql) whenever you have a complex query, especially when using Oracle, at least from my experiences with it in the past (has been some time, so I might be wrong.)

Linq query with multiple subqueries

4 Answers4