Bulb Flash:- Firing a Complex SQL Query using Entity Framework 4.0

In one of our recent projects, we decided to swap SQL Express with SQL Compact to ensure easy installation on client machines…Though the move was smooth there was one hitch….SQL Compact does not support Stored Procedures!!!

And here we were with lots of  medium  complexity SPs with Joins and GroupBys ,OrderBy and Min Max functions!

For Example

select top(@number) p.ProjectName as ProjectName, p.ProjectID as ProjectID,c.Client_Name as ClientName,Max(pl.EndTimeStamp) as LastTimeWorked
from Projects as p join ProgramLog as pl
on
p.ProjectID  =pl.fk_ClientCode
join Clients c
on pl.fk_ClientCode = c.ClientID

Group By p.ClientProjectID, p.ProjectName ,c.Client_Name
Order by MAX (pl.EndTimeStamp) desc

We clearly had two options

1. Replace the stored proc logic with LINQ to Entity queries. We gave that a shot but realized its not easy to write a LINQ query with all the Joins, GroupBys and Max statements etc

2. Fire the SQL query from Entity Framework…We knew this could be done using LINQ to SQL  very easily but hadn’t tried it with the EF yet. Searching online brought us to the “ObjectQuery” feature . You can read more about it here.

Am not going to get into the details because this didn’t work for us also. Although ObjectQuery works well for simple statements( even parameterized), it didn’t work for us for using “Joins”. We tried many options including those given here but to no avail..

What worked!!

What did work for us was this really simple and beautiful feature provided in EF 4.0 called ExecuteStoreQuery<>

So the solution was as simple as

string query = @"select top(@number ) p.ProjectName as ProjectName, p.ClientProjectID as ProjectID,c.Client_Name as ClientName,Max(pl.EndTimeStamp) as LastTimeWorked
           from Projects as p join ProgramLog as pl
           on
           p.ClientProjectID  =pl.fk_ClientCode
           join Clients c
           on pl.fk_ClientCode = c.ClientID

           Group By p.ClientProjectID, p.ProjectName ,c.Client_Name
           Order by MAX (pl.EndTimeStamp) desc";
           var args = new DbParameter[] { new SqlParameter { ParameterName = "number", Value = count } };
           var result = entityContext.ExecuteStoreQuery<MostRecentProjects>(query, args);
           List<MostRecentProjects> resultList = result.ToList();

If you’ve been following closely you will notice I have the ExecuteStoreQuery method returning a list of MostRecentProjects…Since my query does not return a previously generated “entity” but involves a join statement returning a mix of values , this is not an edmx generated class but a simple custom class I created which has one property for each value returned by the query and the ExecuteStoreQuery method was nice enough to fill it for me!!(This was of course trial and error and are we glad it worked!!!)

This is what the class looks like

public partial class MostRecentProjects
  {
      public int ProjectID { get; set; }
      public string ProjectName { get; set; }
      public string ClientName { get; set; }
      public DateTime LastTimeWorked { get; set; }

  }

Hope this helps!

Until Next Time!

Cennest!