none
How to convert SQL to Linq RRS feed

  • Question

  • Hi, I try to convert my sql to linq but the following sql statement I don't know how to convert it.  Please help?

    select DeliveryNote.DeliveryNoteNumber, COUNT(Distinct(SalesOrderLine.ProductID)), Sum(DeliveryNoteLine.Quantity)
    from
    SalesOrder 
    inner join SalesOrderLine on SalesOrderLine.SalesOrderID = SalesOrder.ID and SalesOrder.IsLatestRevision = 1
    inner join DeliveryNoteLine on DeliveryNoteLine.SalesOrderLineRootID = SalesOrderLine.RootID
    inner join DeliveryNote on DeliveryNote.ID = DeliveryNoteLine.DeliveryNoteID and DeliveryNote.IsLatestRevision = 1
    group by
    DeliveryNoteNumber
    order by
    DeliveryNoteNumber

    Thank you

    Eagle
    Wednesday, June 30, 2010 8:32 AM

Answers

  • var query =
    from so in SalesOrders 
    join sol in SalesOrderLines on so.ID equals sol.SalesOrderID
    join dnl in DeliveryNoteLines on sol.RootID equals dnl.SalesOrderLineRootID
    join dn in DeliveryNotes on dnl.DeliveryNoteID equals dn.ID
    where so.IsLatestRevision == 1 && dn.IsLatestRevision == 1
    group new {
    dn.DeliveryNoteNumber
    , dnl.Quantity } by dn.DeliveryNoteNumber into g
    orderby g.Key
    select new
    {
    	DeliveryNoteNumber = g.Key,
    	ProductCount = (
    		from sol1 in SalesOrderLines 
    		join dnl1 in DeliveryNoteLines on sol1.RootID equals dnl1.SalesOrderLineRootID
    		where dnl1.DeliveryNote.DeliveryNoteNumber == g.Key
    		select sol1.ProductID
    	).Distinct().Count()
    	,	
    	QuantitySum = g.Sum( x => x.Quantity )
    };
    Thursday, July 1, 2010 9:56 PM

All replies

  • Did you tried Linqer, is a sql to linq converter

    Regards

    Wednesday, June 30, 2010 2:00 PM
  • It is not work.  g.Count(x=> x.ProductID) need to return boolean

    And I need to count distinct ProductID

     


    Eagle

    Wednesday, June 30, 2010 5:36 PM
  • var query =
    from so in SalesOrders 
    join sol in SalesOrderLines on so.ID equals sol.SalesOrderID
    join dnl in DeliveryNoteLines on sol.RootID equals dnl.SalesOrderLineRootID
    join dn in DeliveryNotes on dnl.DeliveryNoteID equals dn.ID
    where so.IsLatestRevision == 1 && dn.IsLatestRevision == 1
    group new {dn.DeliveryNoteNumber
    ,sol.ProductID
    , dnl.Quantity } by dn.DeliveryNoteNumber into g
    orderby g.Key
    select new
    {
    	DeliveryNoteNumber = g.Key,
    	ProductCount = g.Distinct().Count (),	
    	QuantitySum = g.Sum( x => x.Quantity )
    };
    query.Dump();
    Wednesday, June 30, 2010 11:02 PM
  • This is wrong result with your query.  g.Distinct will distinct all row from grouping not ProductID.

    For example

     

    SalesOrder
    ID = 1
    ID = 2

    SalesOrderLine
    RootID = 1, SalesOrderID = 1, ProductID = 1
    RootID = 2, SalesOrderID = 1, ProductID = 1
    RootID = 3, SalesOrderID = 2, ProductID = 1

    DeliveryNote
    ID = 1, DeliveryNoteNumber = 001
    ID = 2, DeliveryNoteNumber = 002

    DeliveryNoteLine
    ID = 1, DeliveryNoteID = 1, SalesOrderLineRootID = 1, Quantity = 10
    ID = 2, DeliveryNoteID = 1, SalesOrderLineRootID = 2, Quantity = 5
    ID = 3, DeliveryNoteID = 2, SalesOrderLineRootID = 3, Quantity = 1

    Your query will get following result
    DeliveryNoteNumber=001, ProductCount = 2,  Quantity = 15
    DeliveryNoteNumber=002, ProductCount = 1,  Quantity = 1

    Actually I expected following result
    DeliveryNoteNumber=001, ProductCount = 1,  Quantity = 15
    DeliveryNoteNumber=002, ProductCount = 1,  Quantity = 1

     


    Eagle
    Thursday, July 1, 2010 5:16 PM
  • var query =
    from so in SalesOrders 
    join sol in SalesOrderLines on so.ID equals sol.SalesOrderID
    join dnl in DeliveryNoteLines on sol.RootID equals dnl.SalesOrderLineRootID
    join dn in DeliveryNotes on dnl.DeliveryNoteID equals dn.ID
    where so.IsLatestRevision == 1 && dn.IsLatestRevision == 1
    group new {
    dn.DeliveryNoteNumber
    , dnl.Quantity } by dn.DeliveryNoteNumber into g
    orderby g.Key
    select new
    {
    	DeliveryNoteNumber = g.Key,
    	ProductCount = (
    		from sol1 in SalesOrderLines 
    		join dnl1 in DeliveryNoteLines on sol1.RootID equals dnl1.SalesOrderLineRootID
    		where dnl1.DeliveryNote.DeliveryNoteNumber == g.Key
    		select sol1.ProductID
    	).Distinct().Count()
    	,	
    	QuantitySum = g.Sum( x => x.Quantity )
    };
    Thursday, July 1, 2010 9:56 PM
  • Oh...my god.

    The generate SQL performance too bad, compare with original SQL in query plan

    The following is generate SQL

    SELECT [t4].[DeliveryNoteNumber] AS [Key], [t4].[value] AS [Quantity], (

        SELECT COUNT(*)

        FROM (

            SELECT DISTINCT [t6].[ProductID]

            FROM [SalesOrder] AS [t5]

            INNER JOIN [SalesOrderLine] AS [t6] ON ([t5].[ID] = [t6].[SalesOrderID])

            INNER JOIN [DeliveryNoteLine] AS [t7] ON [t6].[RootID] = [t7].[SalesOrderLineRootID]

            INNER JOIN [DeliveryNote] AS [t8] ON ([t7].[DeliveryNoteID] = [t8].[ID])

            WHERE [t8].[DeliveryNoteNumber] = [t4].[DeliveryNoteNumber]

            ) AS [t9]

        ) AS [Count]

    FROM (

        SELECT SUM([t2].[Quantity]) AS [value], [t3].[DeliveryNoteNumber]

        FROM [SalesOrder] AS [t0]

        INNER JOIN [SalesOrderLine] AS [t1] ON ([t0].[ID] = [t1].[SalesOrderID])

        INNER JOIN [DeliveryNoteLine] AS [t2] ON [t1].[RootID] = [t2].[SalesOrderLineRootID]

        INNER JOIN [DeliveryNote] AS [t3] ON ([t2].[DeliveryNoteID] = [t3].[ID])

        GROUP BY [t3].[DeliveryNoteNumber]

        ) AS [t4]

    order by

    DeliveryNoteNumber

     


    Eagle
    Friday, July 2, 2010 1:46 AM
  • Hi Eagle,

     

    SQL Server will help us to optimize the SQL statements again.  J    The SQL statements have been improved a lot in EF4, http://blogs.msdn.com/b/adonet/archive/2009/08/05/improvements-to-the-generated-sql-in-net-4-0-beta1.aspx.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 6, 2010 9:25 AM
    Moderator
  • Actually, generated SQL statement from .Net 4.0. And I used generated SQL compare original SQL in Query Plan.  The performance is 57% and 43%
    Eagle
    Tuesday, July 6, 2010 11:31 AM
  • Hi Eagle,

     

    So I do think we have much to improve in the next release of EF  J    

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 8, 2010 8:30 AM
    Moderator