Connect

  • Connect with me

  • Sunday, September 05, 2010

    Entity Framework : First query using Entity Framework

    Background

    In one of my earlier post, I had demonstrated how to use code first approach with the new Entity Framework 4.0 CTP. In this post I thought of writing simple queries against an existing database table using entity framework 4.0. This is the first post of the Entity Framework 4 learning series.

    Pre-requisites

    I don’t want to go though the steps of creating the database from the scratch. So I chose to use an existing database so that I can save some time required for populating the data in the tables. I have used the existing Northwind database for this purpose which can be downloaded from Microsoft site. This download also contains other sample database which is Pubs.
    Once the installer is executed it creates a directory in the following location “C:\SQL Server 2000 Sample Databases”. I prefer to copy the database into the project folder.

    Generate model from existing database schema

    I have created a simple console application. In order to generate the conceptual entity model based on persistence model or database schema we can add a new item to the project by selecting ADO.NET Entity Data Model option from the wizard.


    image

    In the name box specify name as Northwind.edmx file. The wizard will prompt for choosing the model contents. We should select the Generate from database option. I can select the location of Northwind.mdb file and add it to the output folder of the project. The wizard also provides an option to store the connection string in the App.config file as shown below


    image

    We can choose from Tables, Views or Stored Procedures as source of our model objects. I chose Orders table for the purpose of this demo. Click on finish and Visual Studio will do some black magic behind the scenes to come up with a entity model designer window similar to the one shown below


    image

    In this example I have chosen only one table, but you can add any number of tables onto the designer and visual studio will automatically figure out the relationships and display them on the designer surface. I’ll cover that in some other post. For the time being I’ll start with querying the Orders table from the database.

     

    Query database using entity model

    To keep the example simple I would like to do couple of things
    • Get the count of orders in the Orders table
    • Display the OrderID for the first 20 orders

    First and foremost I need an object context which can be used to run LINQ queries against the conceptual model. Once we generate the entity model using the wizard, Visual Studio creates a class which inherits from the ObjectModel class. It also exposes each entity as a property of the object model class. This enables us to use strong types while querying the entities.

    NORTHWNDEntities context = new NORTHWNDEntities();

     

    Console.WriteLine("Number of orders : " + context.Orders.Count());

     

    var orders = context.Orders.Take(20);

     

    foreach (var order in orders)

    {

          Console.WriteLine("Order ID : " + order.OrderID);

    }

     
    As can be seen from above lines of code, I have created the context object which I made use of in the statement writing the count of orders to console output. The Orders property is strongly typed and Count() extension method gives me the count of orders in the database.

    Because the number of orders are in excess of 800, I thought of restricting the output to first 20 orders. So I have made use of the Take() extension method of LINQ to filter only the top 20 records. I then loop over the collection and display the OrderID for each of those orders.


    That’s all the 5 lines of code needed to get the count and also display the first 20 order ID’s from database. Isn’t it simple compared to the amount of code we would have to write using ADO.NET code? I thought it was faster than preparing Maggi noodles :)


    Conclusion


    It was fairly easy to get started with Entity Framework. Using LINQ with entity model provided type safe way querying the collections. Hope this helps. I have just touched upon a very basic LINQ methods here. We can run all other methods like Where, Single, SelectMany etc.


    As always I have uploaded the complete source code EntityFramework_GettingStarted.zip.


    Until next time Happy programming :)



    6 comments:

    1. I liked the initiative towards EF ORM but it has so many limitations, I was trying to use EF with WCF Data Services with VS2008 SP1 and Silverlight, below are the few short comings I found
      1. LINQ query supports join but we can select only single entity in case of DataServiceQuery.BeginExecute (In real time application we need to get attributes from different entities)
      2. If we make any changes in Database like deleting a column in table "Update Data Model" doesn't work properly (we need to update xml manually otherwise it currupts the edmx file)
      3.No easy approach available to expose custom Stored Procedure which returns a result set.
      And so many other pitfalls...
      So as of now I won't say thumbs up unless it gets enough matured... still a long way to go...

      ReplyDelete
    2. @Nilesh - nice starter post! :)

      @Mridul,much has changed. re #1 : wcf data services client api supports projections re #2 EDMX designer supported mods to database since VS2008 release. re #3: VS2008/.NET 4 allows mapping stored procs to complex types (not entities) which enables returning any resultset you want (but single type...not multiple result sets) Take another look! :)

      ReplyDelete
    3. i have problem with the entity framwork query
      I have two tables
      Table [A]
      ID,NR,MM ART (all are int fields)

      Table [B]
      ID,IDSHTaeterGr[foreign key ID in A],Bez(varchar),IDSK

      I need column as from both tables based on IDSK as parameter.Below given is the code

      public IQueryable GetSHTaeterGrLists()
      {
      Int32 idsk = HttpContext.Current.Session["IDSK"].ToStringOrDefault().ToIntOrDefault(System.Configuration.ConfigurationManager.AppSettings["IDSK"].ToIntOrDefault());
      var q = this.ObjectContext.A.Include("B").Where(c => this.ObjectContext.B.Where(ch => ch.IDSK==idsk).Select(ch => ch.IDSHTaeterGr).Contains(c.ID));
      string a = ((System.Data.Objects.ObjectQuery)q).ToTraceString();
      return q;
      }

      But this expression does not return the exact result based on IDSK it returns the all IDSK.

      Please suggest.

      Thanks,
      Rajnish

      ReplyDelete
    4. Anonymous5:59 AM

      Visit www.entityframeworktutorial.net for basic to expert level learning.

      ReplyDelete
    5. Anonymous9:10 PM

      thanks...

      good article...

      ReplyDelete
    6. Anonymous10:54 PM

      Good Article..

      ReplyDelete