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

Question

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 desc

Answer 1

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 desc

 

Top

 

http://msdn.microsoft.com/en-us/vcsharp/aa336757#TakeSimple

 

Join

 

http://msdn.microsoft.com/en-us/vcsharp/ee908647#crossjoin

 

Group by

 

http://msdn.microsoft.com/en-us/vcsharp/aa336754.aspx#simple1

 

You have one for VB.NET too.

 

http://msdn.microsoft.com/en-us/vcsharp/aa336746

 

You can practice with this.

 

http://www.linqpad.net/

 

Answer 2

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".

Answer 3

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.