Overview

To make it short :

  • LinQ to SQL only works with SQL Server but it’s working great. It’s simple, supports all LinQ operations and optimizes the generated SQL queries.
  • LinQ to Entities on ADO.Net works with lots of database but it doesn’t behave like LinQ to SQL and doesn’t support some key LinQ features.

Now the long story :

For the last project I’ve been working on, we wanted to use LinQ and still be able to switch to one database to an other (MSSQL to MySQL for instance). So we decided we would use LinQ to entity. The generated object are roughly the same, the code to Create/Read/Update/Delete data is roughly the same. So we were quite happy.

Deffered loading

When you do a LinQ request, the data from the associated table isn’t fetched. There’s in fact no other way, as you would get your whole database if you always fetched all the associated objects of your current query.

Deferred loading consists in loading the data coming from an associated table when you request it (when you access your object property).

Eager loading consists in loading the data when you request it.

In LinQ to entities, deferred loading isn’t supported. You have to use eager loading with the .Include(“Property”) LinQ method.

var query = from u in Db.User.Include( "Group" ) select u;

But in fact, you can do some deferred loading very easily in LinQ to Entities :

var query = from u in Db.User select u;
 
foreach( var user in query ) {
    if ( ! user.GroupReference.IsLoaded )     
        user.GroupReference.Load();
 
    Console.WriteLine( "User={0}, Group={1}", user.Name, user.Group.Name );
}

Non supported LinQ key features

Well, now I only want to select the users 1, 2, 3 :

var users = new int[] { 1, 2, 3 };
var query = from u in Db.User.Include( "Group" ) where users.Contains( u.UserId ) select u;

Oups, that would work on LinQ to SQL, but on LinQ to Entities it doesn’t. LinQ to Entities doesn’t support the .Contains or .Any methods. (Note : these methods are supported since .Net 4.0, see below).

There’s in fact a solution but it requires your request to be typed in a method based LinQ Query :

static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
    Expression<Func<TElement, TValue>> valueSelector,
    IEnumerable<TValue> values
) {
    if ( null == valueSelector ) { throw new ArgumentNullException( "valueSelector" ); }
    if ( null == values ) { throw new ArgumentNullException( "values" ); }
    ParameterExpression p = valueSelector.Parameters.Single();
    // p => valueSelector(p) == values[0] || valueSelector(p) == ...
    if ( !values.Any() ) {
        return e => false;
    }
    var equals = values.Select( value => (Expression) Expression.Equal( valueSelector.Body, Expression.Constant( value, typeof( TValue ) ) ) );
    var body = equals.Aggregate<Expression>( ( accumulate, equal ) => Expression.Or( accumulate, equal ) );
    return Expression.Lambda<Func<TElement, bool>>( body, p );
}

And then it looks like this :

var users = new int[] { 1, 2, 3 };
var query = Db.User.Where( BuildContainsExpression<User, int>( u=> u.UserId, users ) );

Now, let’s say you want to use the Include method in the method based query. Well you can’t. But someone added an extension method to do this :

public static class ObjectQueryExtension {
    public static ObjectQuery<T> Include<T>( this ObjectQuery<T> mainQuery, Expression<Func<T, object>> subSelector ) {
        return mainQuery.Include( ( ( subSelector.Body as MemberExpression ).Member as System.Reflection.PropertyInfo ).Name );
    }
}

You have to use it like that :

var users = new int[] { 1, 2, 3 };
var query = Db.User.Include( u => u.Group ).Where( BuildContainsExpression<User, int>( u=> u.UserId, users ) );

I hope this could help you start or switch to LinQ to entities in case you hesitated.

LEFT JOIN in LinQ to entities

You know how LEFT JOIN works in LinQ, you need a join and DefaultIfEmpty :

var query = 
from user in Db.User
join preferredSuperHero in Db.User_Parameter on 
    new { 
        UserId = user.UserId, 
        ParamName = "PreferredSuperHero" 
    } 
    equals new { 
        UserId  = preferredSuperHero.UderId, 
        ParamName = preferredSuperHero.Parameter.Name 
    }
    into joinedPreferredSuperHero
from preferredSuperHero in joinedPreferredSuperHero.DefaultIfEmpty()
orderby user.Name
select new { User = user, PreferredSuperHero = preferredSuperHero };

Except… DefaultIfEmpty doesn’t works with LinQ-to-entities. You generally can find other solutions, but it will add some difficulties. (Note : this method is supported since .Net 4.0, see below).

Check what is supported

In this page, you can check if the methods you might require are available. The two non supported methods I talked about are supported with the .Net 4.0 (currently only available in bêta). So most of the problem related to the missing methods will be solved.

About switching from one base to an other

Switching from one base to the other can be pretty easy if you have the right conversion tool. If not, you have to do all the work yourself. If you wish to switch to MySQL for instance, you can use the MySQL conversion tool. It converts the table structure, the keys, the foreign keys, the indexes and the data from MSSQL to MySQL.

After that, the generated code is exactly the same but I’m not sure you can use one EDMX model file for multiple databases. It might work by using an external SSDL (instead of an resource embedded file) that you select in the connection String for each database. But I haven’t tested it yet.

My little conclusion

LinQ to entities works pretty well. The non automatic deferred loading is really not a problem, especially since it totally kills performances (it fetches data row by row instead of loading everything from a single request). My biggest problem was the lacking support of some LinQ methods, you can solve most of them easily (it just makes an ugly code).

With the .Net 3.5, I think the multi-database feature offered by LinQ to Entities on ADO.Net worth this little extra effort to make it work. But if you don’t care about this (because you only build a little short-life program), you should stick to LiNQ to (MS)SQL as everything works instantly on it.

With the .Net 4.0, LinQ to entities becomes even more interesting. And that’s a reason to start using it with the .Net 3.5 framework.

Sources