none
da SQL a Linq

    Question

  • Salve...

    mi aiutate a tradurre questa query SQL in Linq To Sql???

    SELECT        Clienti.No_, Clienti.Name, Clienti.City, Clienti.County, Clienti.[Phone No_], Clienti.[VAT Registration No_], SUM(Partite.[Sales (LCY)]) AS Expr2,
                             MIN(Partite.[Due Date]) AS Expr1, MAX(AppuntiOperatore.operatore) AS Expr3, MAX(AppuntiOperatore.dataregistrazione) AS Expr4
    FROM            Clienti INNER JOIN
                             Partite ON Clienti.No_ = Partite.[Customer No_] LEFT OUTER JOIN
                             AppuntiOperatore ON Clienti.No_ COLLATE Latin1_General_CI_AS = AppuntiOperatore.codicecliente
    GROUP BY Clienti.No_, Clienti.Name, Clienti.City, Clienti.County, Clienti.[Phone No_], Clienti.[VAT Registration No_]
    HAVING        (MIN(Partite.[Due Date]) < GETDATE())

    Non so come fare una query linq con tre tabelle in join. Un join inner ed un right e poi fare il Group :-(

     


    C#

    • Moved by CoolDadTxMVP Friday, January 31, 2014 3:14 PM SQL related
    Friday, January 31, 2014 1:01 AM

All replies

  • Hi,

    this is a English speaking forum, the answer in English.

    A LEFT JOIN can be done with DefaultIfEmpty, see for example:
    http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join

    But at first you should try to rewrite the SQL query, as the GROUP BY is inefficient and makes a rewrite in LINQ To SQL more complex. Please try if the following query gives you the same result:

    SELECT 
        c.No_, 
        c.Name, 
        c.City, 
        c.County, 
        c.[Phone No_], 
        c.[VAT Registration No_], 
        
        p.SumSales,
        p.MinDueDate, 
        
        a.MaxOpertore,
        a.MaxDataregistrazione
    FROM Clienti AS c
    INNER JOIN (SELECT 
               [Customer No_] AS No_,
               SUM([Sales (LCY)]) AS SumSales,
               MIN([Due Date]) AS MinDueDate
            FROM Partite 
            WHERE [Due Date] < GETDATE() 
            GROUP BY [Customer No_]) AS p ON c.No_ = p.No_
    LEFT JOIN (SELECT 
            codicecliente AS No_
            MAX(operatore) AS MaxOpertore, 
            MAX(dataregistrazione) AS MaxDataregistrazione
        FROM AppuntiOperatore 
        GROUP BY codicecliente) AS a ON c.No_ /* COLLATE Latin1_General_CI_AS */ = a.No_
    
    

    I assigned some descriptive column aliases for the aggregate expressions and changed the HAVING clause into a WHERE clause. A COLLATE clause isn't supported by LINQ To SQL, so please try if the query works without it - if not you will have to use an additional view.

    If you need more help, tell me if there are some issues with rewritten query, please.

    Regards, Elmar

    Friday, January 31, 2014 10:21 AM
  • Hello ,

    A partial translation by Bing Translator for the original post

    help me to translate this SQL query in Linq To Sql?
    SELECT Customers. No _, Clienti.Name, City, Customer Customers. County, Customers.[Phone No _], Customers.[VAT Registration No _], SUM ([Matches.Sales (LCY)]) AS Expr2,
    MIN ([MatchesTwo dates]) AS Expr1, MAX (AppuntiOperatore. operator) AS Expr3, MAX (AppuntiOperatore. dataregistrazione) AS Expr4
    FROM Customers INNER JOIN
    Matches ON Customers. No _ = Games.[Customer No _] LEFT OUTER JOIN
    AppuntiOperatore ON Customers. No COLLATE Latin1_General_CI_AS = codicecliente AppuntiOperatore.
    GROUP BY Customers. No _, Clienti.Name, City, Customer Customers. County, Customers.[Phone No _], Customers.[VAT Registration No _]
    HAVING (MIN ([MatchesTwo Dates]) < GETDATE ())

    I do not know how to do a linq query with three tables in the join. An inner join and a right and then do Group

    For RinoMCP , please , try to reply in English ( Bing Translator is maybe not the best tool but a reply in English will be appreciated )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Saturday, February 01, 2014 12:18 AM
    Moderator
  • Sorry for Italian Language :-)

    I need to translate thir sql query in Linq to sql!!!

    (we have 3 and 2 join. One inner Join and One left join....Results must be grouped)

    SELECT        Clienti.No_, Clienti.Name, Clienti.City, Clienti.County, Clienti.[Phone No_], Clienti.[VAT Registration No_], SUM(Partite.[Sales (LCY)]) AS Expr2,
                             MIN(Partite.[Due Date]) AS Expr1, MAX(AppuntiOperatore.operatore) AS Expr3, MAX(AppuntiOperatore.dataregistrazione) AS Expr4
    FROM            Clienti INNER JOIN
                             Partite ON Clienti.No_ = Partite.[Customer No_] LEFT OUTER JOIN
                             AppuntiOperatore ON Clienti.No_ COLLATE Latin1_General_CI_AS = AppuntiOperatore.codicecliente
    GROUP BY Clienti.No_, Clienti.Name, Clienti.City, Clienti.County, Clienti.[Phone No_], Clienti.[VAT Registration No_]
    HAVING        (MIN(Partite.[Due Date]) < GETDATE())

    thank's

    Rino


    C#

    Saturday, February 01, 2014 8:38 AM