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!

Advertisements

EF 4.0 Bulb Flash!:- Load only what you need!!

A small tip for those working with Entity Framework 4.0:- We all know the concept of Lazy Loading in the Entity Framework: With lazy loading enabled, related objects are loaded when they are accessed through a navigation property.

With lazy loading a drawback is that an object retrieved from the database comes loaded with all its navigable Objects so you may be querying an “Order” class but it comes loaded with the Order.Customer object .

[Thanks to Julielerman for pointing out this inconsistency.  Lazy Loading loads related entities on Navigation and does not come “loaded” with them]

While you may want this in some cases, it makes sense to disable this feature in performance oriented applications and load only what you need!

As against what is written in MSDN , our experience is that when an entity context object gets created, its LazyLoadingEnabled property is defaulted to true!… This is also a reported issue with microsoft

So first step would be to disable the LazyLoadingEnabled!

ProgramEntities entityContext = new ProgramEntities();
entityContext.ContextOptions.LazyLoadingEnabled = false;

List<Order> orderList= entityContext.Orders.ToList();

Next, load what you need!

orderList.ForEach(p => entityContext.LoadProperty(p, "Customer"));

Now you can access the Customer as Order.Customer while ensuring you did not load other related  navigable properties like Order.Contents etc etc.

Hope this helps “lighten” up your code a bit!!

Cennest !!

Bulb Flash:-Returning Complex objects from Stored Procedures in Entity Framework!

Sometimes when using the Entity Framework we decide to encapsulate a complicated piece of SQL into a stored procedure and call it directly from your code.

But how to you extract the result of the Stored Proc if it returns a combination of columns from different tables?

Consider the following Stored Proc which returns a list clients recently worked on.

Notice that columns returned span 2 tables (Joined) so you can’t just make the SP return a List of Clients..

select top(@number)c.Client_Name as ClientName,c.ClientID asClientID,Max(p.EndTimeStamp) asLastTimeWorked
fromClients c joinTimeLog p
on
c.ClientID =p.ClientCode
Group Byc.Client_Name,c.ClientID
Order byMAX(p.EndTimeStamp)desc

So now you add the Stored Procedure in your Entity Diagram, but what will be its return type?

  • If you double click the StoredProc in the Model.Store to get the following screen

image

  • If your SP returns a combination of columns click on the “Get Column Information” button to get the column names returned by the SP

image

  • Click on “Create new Complex Type” to create a new class for this group of columns

image

Now you can use the RecentClients_Results(Rename if you like) as a normal entity class in your code!

Hope this helps!

Until next time!

Cennest

Introducing Code First Entity Framework 4.0!

The ADO.NET team recently came up with a new concept in OR mapping called “Code First” model in Entity Framework 4.0

In addition to supporting a designer-based development workflow, EF4 also enables a more code-centric option which we call “code first development”.  Code-First Development enables a pretty sweet development workflow

While using the Entity Framework, how many times did you need to completely re-create your data-context files just because you changed the data model a tiny bit? And how much time did that waste?…Well if you end up changing your data context frequently and if the database structure is NOT the core focus of your application (i say that because if the Database is VERY complicated it MAY be easier to make the database first anyways), then Code First EF is to your rescue…

Put in simple words:- CF EF 4.0 allows you to

  • First define your data classes as POCO (Plain Old Classes) without any overhead attributes etc
  • Use your POCO classes with a Datacontext just like you would use the classes generated via the Add New Item–>Add Entity Data Model class
  • Generate your database out of the POCO classes by simply specifying a connection string.

Nothing can be simpler than this and no better way to explain this than an example…

I have an MVC app which enabled CRUD operations on an object called “Idea”.

Step1:- Download and installed EF CTP( currently CTP5)

Step1:- Create an IdeaModel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace IdeaMVC.Models
{
    public class IdeaModel
    {
        public int IdeaModelID { get; set; }
        public string IdeaTitle { get; set; }
        public string Description { get; set; }
        public string SubmittedBy { get; set; }

    }

}

As you can see its a POCO model without any decorations

Step2:- Add References

Add Reference to EntityFramework.dll and System.Data.Entity.dll from the .NET tab

Step3:- Create the Datacontext

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace IdeaMVC.Models
{
    public class IdeaContext:DbContext
    {
        public DbSet<IdeaModel> Ideas {get; set;}
    }
}

DbSet and DbContext are from the System.Data.Entity namespace and this will create the datacontext from where you can query your ideas using your favorite Linq to Entities!

Step4:- Define where to store the data

This is where you dictate where the data will reside. Place the connection string in the config file

<configuration>
  <connectionStrings>
    <add
      name="IdeaContext"
      providerName="System.Data.SqlClient"
      connectionString="Server=.\SQLEXPRESS;Database=Products;Trusted_Connection=true;"/>
  </connectionStrings>

The current connection string will create a database called Products  in the local SQL Server if it doesn’t already exist. Your connection string could very well have pointed to an attached DB or SQL CE

A few important points here

  1. The name of the connection string should be the name of the Datacontext (which in our case is IdeaContext and hence the connection string is also called IdeaContext)
  2. If you want to specifically rename the connection string you can do that by passing the new connection string name to the base DataContext constructor . For Example
    public IdeaContext():base("MyConnectionString"){}

  3. If the database is not present at the specified location it will be specifically created based on the DataModel
  4. If the database is already present its schema is matched to the existing datamodel schema to check for updates.

Creating the database from the datamodel follows some rules . For example, it tries to identity the PK as the name of the datamodel+ID so in this case it recognizes the IdeaModelID as the PK. If your PK is defined differently you can specify a [Key] attribute  from the .NET 4.0 System.Component.DataAnnotations namespace to indicate the PK.

Many configurations are possible but for now lets take the most basic case and continue.

OK so we defined the model, the datacontext and the connection string…Next we need to use the datacontext…

Since i am using the MVC model, i will access my list in the Index method  (Note that this can be used in any .NET app)

So my Index method looks like this

 IdeaContext context= new IdeaContext();
 var j = context.Ideas;
 return View(j.ToList());

 Lets also take a quick look at the Create method which contains the following code

  [HttpPost]
        public ActionResult Create(IdeaModel  idea)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    idea.IdeaID = new Random().Next();
                    IdeaContext context = new IdeaContext();
                    context.Ideas.Add(idea);
                    context.SaveChanges();
                    return View("Details", idea);
                }
                else
                {
                    return View("Create", idea);
                }
            }
            catch (Exception ex)
            {
                return View();
            }
        }

So the datacontext usage is the EXACT same as in a Database first approach..

Now lets take a look at my SQL Server DB list to ensure i am not bluffing you:-)

image

So there is no database called products here…

Now lets run the application

image

Click Create New to create the new Idea

image

Now lets check the database again

image

Along with the Products DB with the expected IdeaModels table, you see one more table called EdmMetadata….This table is responsible for monitoring the mapping between the code schema and the database…if you change the code schema without changing the DB schema you will get an exception…There are three options here

1. Manually make the changes in the DB schema also

2. Manually Delete the database and run the code to recreate it

3. Auto -Recreate the DB by Adding

DbDatabase.SetInitializer(new DropCreateDatabaseIfModelChanges<IdeaContext>());

at the application startup. My app being an MVC model i added this at the Application_Start()

This was a very brief introduction to a very easy and extremely useful approach to working with Databases..

I found the following two blog posts most helpful in this context…so do go through them in details.(in the order given as Scott Gu’s blog is of CTP 4 so some terms changed in CTP 5 which is the current CTP as of the time this blog post was written!)

http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

http://blogs.msdn.com/b/adonet/archive/2010/12/06/ef-feature-ctp5-code-first-walkthrough.aspx

Hope this is helpful!

Cennest