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

  • 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
    Monday, 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 desc
     
    Top
     
     
    Join
     
     
    Group by
     
     
    You 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