Pitfalls in migrating for Linq2SQL to Entity Framework

by Jon

Development

LINQ2SQL and Entity Framework are both powerful LINQ providers for database access, that appeared early in the days of LINQ. Since then Entity Framework has been more actively developed, has greater support for databases other than SQLServer (though the Mono version does support other databases) and greater support from other tooling. As such while LINQ2SQL is still a capable provider, if you chose to use LINQ2SQL in the days when there was less to choose between them, you may find that you want to migrate to Entity Framework.

When this is easy, it’s extremely easy; since they’re both using the same IQueryable operations from LINQ, many queries will work exactly the same as they did before, and for most consuming code (that which uses the entities rather than defining them) most of the migration can be done with a couple of find-and-replace operations.

There are though definitely some difficulties that can be encountered.

Function Support

One of the greatest of these is if you were making heavy use of database functions in your LINQ2SQL project. Function support remains simpler in LINQ2SQL than in Entity Framework, and while Entity Framework now has better support than it once did for database-first development using functions, its support for code-first remains lacking. Migrating an existing project from one LINQ provider to another is neither strictly database-first nor code-first, since one starts with both a database and working code, but it’s closer to code-first.

The EntityFramework.Functions library allows for such a migration, though there are still some difficulties. One we encountered is that having a relatively complicated set of DbContexts with an inheritance relationship to each other, and with multiple schemata in the database, the logic that matched method names to SQL function names was incorrect. We resolved that by changing the logic from matching on the FullName property to matching on the Name property alone. However this would not be a useful change for everyone and hence worth contributing back to that project; if you have more than one method-function pair using the same name, this would make things worse for you, rather than better.

Neither First nor Last

There are a lot of cases where Entity Framework will not allow First() or Last() to be used within a query, but will allow FirstOrDefault() or LastOrDefault() though the result is logically identical. Changing from one to the other is easy, but since First() and Last() describe the intent better (the “or default” case cannot possibly come up) it’s possible that one may have many such uses that have to be updated, and of course changes have to be checked to make sure that a logical error doesn’t sneak in.

This is one of the easier pitfalls to deal with when migrating to Entity Framework, but checking for it will still take time.

ComplexType Complexity

Entity Framework has better support for “complex types” which don’t map well to entities. Unfortunately, it also insists on using either those or an entity when it comes to the result of a function, but will not let you create one directly within a query.

This was a difficulty to us as we had both code like:

public IQueryable<NameForID> PreferredHotelNames(string languageCode)

…that was mapped to a database function, and code like:

public IQueryable<NameForID> PreferredDescriptionLanguage(string languageCode)
{
    return
        from hds in HotelDescriptionSections
        orderby LanguageMatchRating(languageCode, hds.LanguageCode), hds.Priority
        group new NameForID{ID = hds.HotelID, Name = null, LanguageCode = hds.LanguageCode}
        by hds.HotelID into hdsGroups
            select hdsGroups.First();
}

…that used the same type in a reusable query that also had to remain composable as part of more complex queries.

So one is caught in a literal dilemma; if NameForID is defined as a complex type the first method will work, and the second will not, and vice versa.

This can be resolved quite neatly by defining a derived class:

protected class NameForIDDTO : NameForID
{
}

We can now allow NameForID to have a ComplexTypeAttribute that isn’t inherited by NameForIDDTO. The first method works, and the second can be rewritten thus:

public IQueryable<NameForID> PreferredDescriptionLanguage(string languageCode)
{
    return
        from hds in HotelDescriptionSections
        orderby LanguageMatchRating(languageCode, hds.LanguageCode), hds.Priority
        group new NameForIDDTO{ID = hds.HotelID, Name = null, LanguageCode = hds.LanguageCode}
        by hds.HotelID into hdsGroups
            select hdsGroups.FirstOrDefault();
}

Because of the covariance of IQueryable<T>, the signature doesn’t have to change and as far as calling code is concerned, including calling code that composes the query into a more complex query, nothing has changed and everything still works as it did before. The new class need not even be visible outside of the point where it is used.

Duplicate Parameter Names

If a query uses two or more database functions, and they have a parameter in common, then the query processor in Entity Framework will unfortunately use those parameter names in the produced SQL query, resulting in an invalid query. This can be mitigated by choosing unique parameter names, but that can’t help in the case of a query that uses the same method more than once. Unfortunately, there is currently nothing that can be done here, other than rewriting the method in question to perform parts of the query directly through Entity Framework and moving the final logic that combines them into LINQ-2-objects. Care is needed to ensure that the result is still efficient.

 

Are you considering a migration? Talk to us at Communicraft about your .NET web development project.



Jon Hanna
Colleague, Warrior, Internet Pioneer
Rest in Power (1976 - 2019)