Need help converting this simple SQL into LinqToSql please. RRS feed

  • Question

  • Hi folks,

        i'm really struggling with this. I use linqpad a lot to do all my linq queries, but i just couldn't get this. I'm trying to get the AVERAGE (with the result as a float) for two fields. I ended up getting this working in SQL .. but i need it in linq, please.


    here's the SQL...


    SELECT AVG(CONVERT(FLOAT,[t2].[Price1])), AVG(CONVERT(FLOAT,[t2].[Price2]))

    FROM [dbo].[TableA] AS [t0]

    inner join [dbo].[TableB] AS [t1] on [t0].PK_ID = [t1].FK_ID

    INNER JOIN [dbo].[TableC] AS [t2] ON [t1].PK_ID = [t2].PK_ID

    WHERE ([t0].[SomeId_1] = 1) AND ([t0].[SomeId_2] = 9)


    (i've replaced the real table names and fields names .. so i hope it's not TOO confusing).


    As proof that i tried to do this myself in linq .. this is the best i came up with (which gives me no results btw).


    var q = (from l in db.TableA

    where l.SomeId_1 == 1 && l.SomeId_2== 9

    select new


    Price1Average = l.Table2.Average(r => r.Table3.Price1),

    Price2Average = l.Table2.Average(r => r.Table3.Price2)




    Table 1 has zero to many table 2's. table 2 has 1 to 0 table 3's.



    Please help!

    Tuesday, May 6, 2008 2:43 PM


  • Could you post the TSQL that your LINQ query generates? I tried a similar one, against Northwind database:


    var query = from c in db.Customers

                      where c.CustomerID != "ALFKI" && c.City !=  "London"

                      select new


                          Avg1 = c.Orders.Average(o => (float?)o.Shipper.ShipperID),

                          Avg2 = c.Orders.Average(o => (float?)o.Shipper.Phone.Length)



    and it seems to be working fine for me (I get the results back), producing the following TSQL:




      SELECT AVG(CONVERT(Real,[t2].[ShipperID]))

      FROM [dbo].[Orders] AS [t1]

      LEFT OUTER JOIN [dbo].[Shippers] AS [t2] ON [t2].[ShipperID] = [t1].[ShipVia]

      WHERE [t1].[CustomerID] = [t0].[CustomerID]

    ) AS [Avg1],


      SELECT AVG(CONVERT(Real,LEN([t4].[Phone])))

      FROM [dbo].[Orders] AS [t3]

      LEFT OUTER JOIN [dbo].[Shippers] AS [t4] ON [t4].[ShipperID] = [t3].[ShipVia]

      WHERE [t3].[CustomerID] = [t0].[CustomerID]

    ) AS [Avg2]

    FROM [dbo].[Customers] AS [t0]

    WHERE ([t0].[CustomerID] <> @p0) AND ([t0].[City] <> @p1)



    Wednesday, May 7, 2008 11:06 PM