Monday, February 12, 2007

EDM and LINQ

In doing some more research on ADO.NET's Entity Data Model (EDM) I came across some videos here and here by Shyam Pather, Dev Lead on the ADO.NET Team. The videos are from last summer but they do a really great job of describing the EDM as a conceptual model of your data, describing properties, associations/relationships, even inheritance -- using much a richer type system to model the entities. It separates the actual normalized data schema away from your application so that when underlying schema changes happen, your application is safe as long as the entity model hasn't changed. With ADO.NET 3.0 you can write queries (with a new 'entity SQL' eSQL query syntax) against the EDM and the data access stack automatically translates them to the database -- instead of getting rows back you get entities. Of course, any changes you make can be tracked and persisted back to the database automatically for you as well.

What I really love about the ADO.NET team is that they pay attention to the backward compatibility and migration issues. What I like here is the easy migration path of current code by using the new Mapping Provider (instead of the Sql Provider). The provider is different but the key is that you can use the connections, commands, readers, etc. in the same manner as you are accustomed to. However if you are writing new code you can create a strongly-typed query using the new Query class that returns the objects instead.

This is all really really really great, however, I'm not too jazzed about learning yet another query syntax just to query these entities. Here's where LINQ comes in. You can query the EDM directly with LINQ, (very similar to how DLinq looks, actually). Let's take a couple examples. Here's how you would access data today:

Using cnn As New SqlConnection(MyConnectionString)
    Using cmd As SqlCommand = cnn.CreateCommand()
        cmd.CommandText = "SELECT Employee.Name, Region.Name " & _
                "FROM SalesPeople " & _
                "INNER JOIN Employees ON SalesPeople.EmployeeId = Employees.EmployeeId " & _
                "INNER JOIN Region ON SalesPeople.RegionId = Region.RegionId"

        Using dr As SqlDataReader = cmd.ExecuteReader()
            'Process DataReader.....
        End Using
    End Using
End Using

Say I created an EDM with an entity called SalesPeople, here's what the query could look like using Entity SQL (eSQL):
Using cnn As New MapConnection(MyConnectionString)
    Using cmd As MapCommand = cnn.CreateCommand()
        cmd.CommandText = "SELECT Name, Region FROM SalesPeople"

        Using dr As IDataReader = cmd.ExecuteReader()
            'Process DataReader.....
        End Using
    End Using
End Using

Finally (using the same EDM) here's what it could look like with LINQ:
Using edm as New MyModel()
 
    Dim sales = From s In edm.SalesPeople Select s
    
    For Each person As SalesPerson In sales
        'Do something with the SalesPerson objects
        Console.WriteLine(person.Name)
    Next

End Using

All this makes me think... why do we need DLinq (LINQ to SQL) at all? Hmmmmmmm????? Seriously.

1 comment:

Qasim said...

Thank very much. Great article

I have three tables
1. Department
2.Courses
3. Version

One department have many courses and one course have many versions

I want to get latest version (where version.Number is Max) by DepartmentId
How I have to make query please?