LINQ - SQL to LINQ - Asked By Shan on 04-May-11 12:40 AM

Hi,

 Sorry for repeating the question,

I want to convert the below query to LINQ,
 

SELECT AVG(currentvalue) AS ChartAvg, CONVERT(varchar(10), ReceivedDate, 103) AS RecDate

FROM DatapointValues

WHERE (CONVERT(varchar(10), ReceivedDate, 103) = '21/04/2026')

GROUP BY CONVERT(varchar(10), ReceivedDate, 103)

I use the below LINQ but not working,

from datapointvalues in db.DatapointValues

where

Convert.ToString(datapointvalues.ReceivedDate) == "21/04/2026"

group datapointvalues by new {

Column1 = Convert.ToString(datapointvalues.ReceivedDate)

} into g

select new {

ChartAvg = (System.Decimal?)g.Average(p => p.currentvalue),

g.Key.Column1

}

HI

refer to this links....you can convert it

http://blogs.msdn.com/b/vbteam/archive/2008/01/08/converting-sql-to-linq-part-7-union-top-subqueries-bill-horst.aspx
http://msdn.microsoft.com/en-us/library/bb425822.aspx
http://msdn.microsoft.com/en-us/library/bb386976.aspx

hope it helps you...

HI

refer this example and try

In Sql

SELECT Book.*, Authors.*,BookNumber.*
FROM Book INNER JOIN 
(Authors INNER JOIN BookAuthors ON Authors.ID = BookAuthors.AuthorID) 
ON Book.ID = BookAuthors.BookID INNER JOIN 
BookNumber ON Book.ID = BookNumber.BookID
 
WHERE AuthorFirst = @AuthorFirst AND AuthorLast = @AuthorLast;
 
In Linq

var query = from b in db.Books
                                   join ba in db.BookAuthors on b.ID equals ba.BookID
                                    join a in db.Authors on ba.AuthorID equals a.ID
                                    join bn in db.BookNumbers on ba.BookID equals bn.BookID
                                    where a.AuthorFirst == splitstring[1].ToString() && a.AuthorLast == splitstring[0].ToString()
                                   select b;

hope it helps you...

hi..

Use the below query and let me know the issues... is there any requirement in your code to convert date into string

from datapointvalues in db.DatapointValues

where

string.Format("{0:MM/dd/yyyy}", datapointvalues.ReceivedDate); == "21/04/2026"

group datapointvalues by

string.Format("{0:MM/dd/yyyy}", datapointvalues.ReceivedDate);

 into g

select new {

ChartAvg = Convert.TODEcimal(g.Average(p => p.currentvalue)),

g.Key.Column1

}
hope this helps you..