Answered by:
How can i convert this sql query to entity framework version 4.0 query?

Question
-
SELECT TOP 10 p.[Description], count(*) TotalFROM tblAbstractProcedure apinner join tblProcedure p on ap.procedureCode = p.CodeWHERE ap.MedicareId = 010033 and ap.databaseid = 20101 and ap.SeqNO = 1group by p.[Description]order by Total descMonday, October 17, 2011 7:02 PM
Answers
-
Hi AlpeshSoni;
The following Linq to SQL query should return the same results as your TSQL query. The names of the DataContext must be changed to the name that you are using and table names may also need to be modified depending on how the dbml file was created.
var results = (from j in (from ap in DataContext.tblAbstractProcedure where ap.MedicareId == 010033 && ap.databaseid == 20101 && ap.SeqNO == 1 join p in DataContext.tblProcedure on ap.procedureCode equals p.Code select new { ap, p }) group j by j.p.Description into jGroup orderby jGroup.Count() descending select new { Description = jGroup.Key, Total = jGroup.Count() }).Take(10);
Fernando (MCSD)
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".- Marked as answer by AlpeshSoni Tuesday, October 18, 2011 1:53 PM
Monday, October 17, 2011 9:40 PM
All replies
-
On 10/17/2011 3:02 PM, AlpeshSoni wrote:> SELECT TOP 10 p.[Description], count(*) Total> FROM tblAbstractProcedure ap> inner join tblProcedure p on ap.procedureCode = p.Code> WHERE ap.MedicareId = 010033 and ap.databaseid = 20101 and ap.SeqNO = 1> group by p.[Description]> order by Total descTopJoinGroup byYou have one for VB.NET too.You can practice with this.Monday, October 17, 2011 8:05 PM
-
Hi AlpeshSoni;
The following Linq to SQL query should return the same results as your TSQL query. The names of the DataContext must be changed to the name that you are using and table names may also need to be modified depending on how the dbml file was created.
var results = (from j in (from ap in DataContext.tblAbstractProcedure where ap.MedicareId == 010033 && ap.databaseid == 20101 && ap.SeqNO == 1 join p in DataContext.tblProcedure on ap.procedureCode equals p.Code select new { ap, p }) group j by j.p.Description into jGroup orderby jGroup.Count() descending select new { Description = jGroup.Key, Total = jGroup.Count() }).Take(10);
Fernando (MCSD)
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".- Marked as answer by AlpeshSoni Tuesday, October 18, 2011 1:53 PM
Monday, October 17, 2011 9:40 PM -
Hi darnold924,
Thanks for the links to the useful articles. I actually refered to those but was just confused a little bit about how can i perform the group by and count(*) in entity framework just like in my sql. And thanks a ton for the information about linqpad.net. Its an amazing tool.
- Edited by AlpeshSoni Tuesday, October 18, 2011 2:09 PM
Tuesday, October 18, 2011 1:56 PM