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; 4 Answers
Answers 1
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(); Answers 2
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; } } } Answers 3
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
Answers 4
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.)
0 comments:
Post a Comment