| | Data providers supported.
| Data Provider | Provider Name | Namespace | Vendor |
|---|
| AccessDataProvider | Access | System.Data.OleDb | Microsoft |
| DB2DataProvider | DB2 | IBM.Data.DB2 | IBM |
| InformixDataProvider | Informix | IBM.Data.Informix | IBM |
| FdpDataProvider | Fdp | FirebirdSql.Data.FirebirdClient | Firebird |
| MySqlDataProvider | MySql | MySql.Data.MySqlClient | Sun Microsystems | | OdpDataProvider | ODP | Oracle.DataAccess.Client | Oracle |
| PostgreSQLProvider | PostgreSQL | Npgsql | PostgreSQL.org |
| SqlCeDataProvider | SqlCe | System.Data.SqlServerCe | Microsoft |
| SQLiteDataProvider | SQLite | System.Data.SQLite | SQLite.org |
| SqlDataProvider | Sql | System.Data.SqlClient | Microsoft |
| SybaseDataProvider | Sybase | Sybase.Data.AseClient | Sybase |
![]() |
Operators.
| | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access |
|---|
| Bitwise AND | a & b | BitAnd(a, b) | Bin_And(a, b) | a & b | X |
| Bitwise OR | a || b | BitOr(a, b) | (a + b) - BitAnd(a, b) | Bin_Or(a, b) | a || b | X |
| Bitwise XOR | a ^ b | BitXor(a, b) | (a + b) - BitAnd(a, b) * 2 | Bin_Xor(a, b) | a # b | a ^ b | (a + b) - (a & b) * 2 | X |
| Coalesce | a ?? b | Coalesce(a, b) | Nvl(a, b) | Coalesce(a, b) | Iif(a IS NULL, b, a) |
| a ?? b ?? c | Coalesce(a, b, c) | Nvl(a, b, c) | Coalesce(a, b, c) | Iif(a IS NULL, Iif(b IS NULL, c, b), a) |
| Concatenation | a + b + c | a || b || c | Concat(a, b, c) | a + b + c | a || b || c | a + b + c |
| Conditional | a ? b : c | CASE WHEN a THEN b ELSE c END | Iif(a, b, c) |
| a ? b : c ? d : e | CASE WHEN a THEN b WHEN c THEN d ELSE e END | Iif(a, b, Iif(c, d, e)) |
| Modulo | a % b | Mod(a, b) | a % b | a MOD b |
Core string functions.
| | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access |
|---|
| Case | Sql.Lower(s) | Lower(s) | LCase(s) |
| Sql.Upper(s) | Upper(s) | UCase(s) |
| CharIndex | Sql.CharIndex(a, s) | Locate(a, s) | X | InStr(s, a) | X | Position(a in s) | Locate(a, s) | CharIndex(a, s) | InStr(1, s, a, 1) |
| Sql.CharIndex(a, s, b) | Locate(a, s, b) | X | InStr(s, a, b) | X | Position(a in Substring(s, b,
Length(s)-b)) + b-1 | Locate(a, s, b) | CharIndex(a, s, b) | CharIndex(a,Substring(
s, b, Len(s)-b)) + b - 1 | CharIndex(a, s, b) | InStr(b, s, a, 1) |
| Length | Sql.Length(s) | Length(s) | Char_Length(s) | Length(s) | Len(s) | Length(s) | Len(s) |
| LIKE | !s.Contains(a) | s NOT LIKE a | NOT s LIKE a | s NOT LIKE a |
| s.Contains("a%b") | s LIKE '%a~%b%' ESCAPE '~' | s LIKE "%a[%]b%" |
| s.Contains("abc") | s LIKE '%abc%' |
| s.Contains(str) | s LIKE @str ESCAPE '~' | s LIKE @str |
| s.EndsWith("abc") | s LIKE '%abc' |
| s.StartsWith("abc") | s LIKE 'abc%' |
| Sql.Like(s, "%abc%") | s LIKE '%abc%' |
| Sql.Like(s, "a~%b", '~') | s LIKE "a~%b" ESCAPE '~' | s LIKE "a[%]b" |
| SqlMethods.Like(s, "%abc%") | s LIKE '%abc%' |
| SqlMethods.Like(s, "a~%b", '~') | s LIKE "a~%b" ESCAPE '~' | s LIKE "a[%]b" |
| Pad | Sql.PadLeft(s, a, b) | VarChar(Repeat(b,
a-Length(s)), 1000) || s | LPad(s, a, b) | Replicate(b, a - Len(s)) + s | String(a-Len(s), b)) + s |
| Sql.PadRight(s, a, b) | s || VarChar(Repeat(
b, a-Length(s)), 1000) | RPad(s, a, b) | s + Replicate(b, a - Len(s)) | s + String(a-Len(s), b)) |
| Replace | Sql.Replace(s, a, b) | Replace(s, a, b) | Str_Replace(s, a, b) | Replace(s, a, b) | X |
| Reverse | Sql.Reverse(s) | X | Reverse(s) | Reverse(s) -- implemented as UDF | Reverse(s) | X | Reverse(s) | X |
| Space | Sql.Space(a) | VarChar(
Repeat(' ', a), 1000) | RPad(' ', a, ' ') | Repeat(' ', a) | Space(a) | PadR(' ', a) | Space(a) |
| Stuff | Sql.Stuff(s, a, b, c) | Substring(s, 1, a-1) + c + Substring(s, a+b, Length(s)-a-b+1)) | Stuff(s, a, b, c) | Substring(s, 1, a-1) + c +
Substring(s, a+b, Length(s)-a-b+1)) |
| Substring | Sql.Left(s, a) | Left(s, a) | Substr(s, 1, a) | Left(s, a) | Substring(s, 1, a) | Left(s, a) | Substring(s, 1, a) | Left(s, a) | LeftStr(s, a) | Left(s, a) |
| Sql.Right(s, a) | Right(s, a) | Substr(s,
Length(s)-a+1, a) | Right(s, a) | Substring(s, Length(s)-a+1, a) | Right(s, a) | Substring(s,
Len(s)-a+1, a) | Right(s, a) | RigthStr(s, a) | Right(s, a) |
| Sql.Substring(s, a, b) | Substr(s, a, b) | Substring(
s from a for b) | Substring(s, a, b) | Substr(s, a, b) | Mid(s, a, b) |
| Trim | Sql.Trim(s) | Trim(s) | LTrim(RTrim(s)) | Trim(s) |
| Sql.TrimLeft(s) | LTrim(s) |
| Sql.TrimRight(s) | RTrim(s) |
Extended Linq string functions.
To support methods listed below provider must implement all used functions.
| | Linq | All data providers |
|---|
| Case | s.ToLower () | Sql.Lower(s) |
| s.ToUpper () | Sql.Upper(s) |
| Compare | s.CompareTo (a) | s > a ? 1 : s == 0 ? 0 : -1 |
| IndexOf | s.IndexOf (a) | Sql.Length(a) == 0 ? 0 : (Sql.CharIndex(a, s) ?? 0) - 1 |
| s.IndexOf (a, b) | Sql.Length(a) == 0 && Sql.Length(s) > b ? b : (Sql.CharIndex(a, s, b + 1) ?? 0) - 1 |
| s.IndexOf (a, b, c) | Sql.Length(a) == 0 && Sql.Length(s) > b ? b : (Sql.CharIndex(a, Sql.Left(s, c), b) ?? 0) - 1 |
| Insert | s.Insert (a, b) | Sql.Length(s) == a ? s + b : Sql.Stuff(s, a + 1, 0, b) |
| LastIndexOf | s.LastIndexOf(a) | Sql.Length(a) == 0 ? Sql.Length(s) - 1 :
(Sql.CharIndex(a, s) ?? 0) == 0 ? -1 :
Sql.Length(s) - (Sql.CharIndex(Sql.Reverse(a), Sql.Reverse(s)) ?? 0) - Sql.Length(a) + 1 |
| s.LastIndexOf(a, b) | Sql.Length(a) == 0 ? b :
(Sql.CharIndex(a, s, b + 1) ?? 0) == 0 ? -1 :
Sql.Length(s) - (Sql.CharIndex(Sql.Reverse(a), Sql.Reverse(Sql.Substring(s, b + 1, Sql.Length(s) - b))) ?? 0) - Sql.Length(a) + 1 |
| s.LastIndexOf(a, b, c) | Sql.Length(a) == 0 ? b :
(Sql.CharIndex(a, Sql.Left(s, b + c), b + 1) ?? 0) == 0 ? -1 :
b + c - (Sql.CharIndex(Sql.Reverse(a), Sql.Reverse(Sql.Substring(s, b + 1, c))) ?? 0) - Sql.Length(a) + 1 |
| Length | s.Length | Sql.Length(s) |
| Pad | s.PadLeft (a) | Sql.PadLeft(s, a, ' ') |
| s.PadLeft (a, b) | Sql.PadLeft(s, a, b) |
| s.PadRight (a) | Sql.PadRight(s, a, ' ') |
| s.PadRight (a, b) | Sql.PadRight(s, a, b) |
| Remove | s.Remove (a) | Sql.Left(s, a) |
| s.Remove (a, b) | Sql.Stuff(s, a + 1, b, "") |
| Replace | s.Replace (a, b) | Sql.Replace(s, a, b) |
| Substring | s.Substring (a) | Sql.Substring(s, a + 1, Sql.Length(s) - a) |
| s.Substring (a, b) | Sql.Substring(s, a + 1, b) |
| Trim | s.Trim () | Sql.Trim(s) |
| s.TrimEnd () | Sql.TrimRight(s) |
| s.TrimStart () | Sql.TrimLeft(s) |
Core datetime functions.
| | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access |
|---|
| Current Timestamp | Sql.CurrentTimestamp | CURRENT_TIMESTAMP | CURRENT | CURRENT_TIMESTAMP | GetDate() | CURRENT_TIMESTAMP | Now |
| Sql.GetDate() | CURRENT_TIMESTAMP | CURRENT | CURRENT_TIMESTAMP | GetDate() | CURRENT_TIMESTAMP | Now |
Extended Linq datetime functions.
| | Linq | All data providers |
|---|
| Current Timestamp | DateTime.Now | Sql.CurrentTimestamp |
SQL transformations.
| | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access |
|---|
| SELECT x | db.Select(() => x) | SELECT x
FROM SYSIBM.SYSDUMMY1
FETCH FIRST 1 ROW ONLY |
| SELECT FIRST 1 x
FROM SYSTABLES |
| | SELECT x
FROM rdb$database |
| |
| TAKE x | query.Take(x) | ... FETCH FIRST x ROWS ONLY |
| | | | | | X | | | |
| |