user597939 user597939 - 1 month ago 6
C# Question

Linq with multiple RDBMS type

I have heard that Linq query write once can be run on SQL and MS Access database too. Is it right or wrong?

For example I want to write queries once regardless of database type like currently I'm using MS Access database and later then if I wish to move on to SQL Server then I don't want to change my queries. Is this possible??


It depends in part on the queries. For simple queries (select, where, orderby) you should be fine, but there are a lot of implementation-specific details.

For example:

  • taking a First on a set you haven't explicity ordered : LINQ-to-SQL is fine with that, LINQ-to-Entities will fail
  • using an Expression.Invoke to build a gnarly custom expression : again, LINQ-to-SQL is fine, LINQ-to-Entities will fail
  • using things like UDFs - in addition to LINQ-to-Entities not supporting it, your RDBMS might not support it
  • with "Astoria" (LINQ to data services), there were some scenarios around .Where(predicate).FirstOrDefault() vs .FirstOrDefault(predicate) - which are semantically identical, but IIRC only one works (in Astoria)

My point is; it might work, but you do need to test against the specific implementation.