none
Linq to SQL - other engines besides MS SQL Server RRS feed

  • Question

  • I like Linq alot, particularly Linq to SQL.

    Recently I've been having to branch out to different database engines besides MS SQL (for interop with other systems) -> SQLite, MySQL, etc.

    In the case of SQLite, 90% of expressions work along with their .NET library that implements IDb.  FirstOrDefault() on a table translates to 'SELECT TOP X', however SQLite doesn't support that expression and appends 'LIMIT X' to the end.  The XOR bitwise operator (don't ask me how I know) also does not translate to SQLite, they must use a different symbol for it.

    In the case of MySQL, no expression works because they use `COLUMN` to delimit a column/table name rather than [COLUMN] as MS SQL does.

    I would expect other Oledb or Odbc providers to break on some expressions as well.

    Is there anything build into Linq to SQL that can workaround these issues with different SQL connectors?  Or any plan to?  

    I am at a juncture, where I might try implement my own.
    Monday, May 25, 2009 3:14 PM

Answers

All replies

  • There is no plan to support other databases nor an API that you can implement yourself.

    You might want to take a look at LINQ to Entities that includes a complete provider model that can be implemented to support additional database vendors.

    [)amien
    Monday, May 25, 2009 7:59 PM
    Moderator

  • I am at a juncture, where I might try implement my own.

    Take a look at Matt Warren's blog:
    http://blogs.msdn.com/mattwar/

    ...and download his IQueryable toolkit:
    http://www.codeplex.com/IQToolkit
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Tuesday, May 26, 2009 12:51 AM
    Answerer
  • I will look into these, they look promising.

    I figured translating linq expressions to SQL might not be such an easy task, but one of those may greatly simplify it.
    Tuesday, May 26, 2009 12:57 PM
  • I was actually able to implement SQLite with the above links with fairly little code, passing all of his unit tests that SQLite could possibly complete.  (XOR and truncate are not implemented by SQLite)

    I will offer this to the author, but if anyone needs it in the mean time--just ask.

    As for MySQL, I need the Northwind database on a MySQL server somewhere... I'll get to it at some point.
    Tuesday, May 26, 2009 6:59 PM