dotConnect for MySQL Documentation
Using Database-Specific Functions in Entity Framework
support@devart.com

dotConnect for MySQL allows using MySQL-specific functions in Entity SQL and LINQ to Entities. You can use these functions in your Entity SQL statements for Entity Framework v4 - v6.

The following Entity SQL example is for ObjectContext only:

C#csharpCopy Code
        var q = from c in db.PersonContacts.Where(@"Devart.Data.MySql.Soundex(it.LastName) == 
            Devart.Data.MySql.Soundex(@new)", new ObjectParameter("new", name))
                select c;

These functions are members of the MySqlFunctions class, that is located in the Devart.Data.MySql.Entity assembly.

The following example can be used both for ObjectContext and for DbContext:

C#csharpCopy Code
        var q = from c in db.PersonContacts
                where MySqlFunctions.Soundex(c.LastName, "Q") == MySqlFunctions.Soundex(name)
                select c;
] [MySQL;PostgreSQL: ]

Entity SQL function

LINQ to Entities function

Description

acos Acos Returns the arc cosine of a number, in radians.
adddate Adddate Returns a date value that represents an argument value plus a specified time interval.
addtime Addtime Returns a datetime value that represents an argument value plus a specified time interval.
aes_decrypt AesDecrypt Decrypts data using the official AES algorithm.
aes_encrypt AesEncrypt Encrypts the specified string with the specified key string, using the official AES algorithm.
ascii Ascii Returns the ASCII code value of the leftmost character of the argument as an integer.
asin Asin Returns the arc sine of a number, in radians.
atan Atan Returns the arc tangent of a number, in radians.
bin Bin Returns a string containing a binary representation of the specified number.
bit_count BitCount Returns the number of set bits.
bit_length BitLength Returns the length of the specified string in bits.
ceilCEIL Ceil Return the smallest integer value that is not less than the specified value.
ceiling Ceiling Return the smallest integer value that is not less than the specified value.
char Char Returns the character that has the code value specified by the argument. Accepts up to 3 arguments and returns a character for each argument specified.
char_length CharLength Returns the length of the specified string in characters.
charset Charset Returns the charset of the argument.
coercibility Coercibility Returns the collation coercibility value of the specified string.
compress Compress If MySQL is compiled with a compression library, compresses the given string and returns it as binary string. Otherwise, returns Null.
concat Concat Concatenates the given strings.
concat_ws ConcatWs Concatenates the given strings and inserts the separator string (the first argument) between them.
connection_id ConnectionId Returns the connection ID for the connection, which is unique among the current connections.
conv Conv Converts numbers between different number bases.
convert_tz ConvertTz Converts the specified datetime from one timezone to another.
cos Cos Returns the cosine of a number.
cot Cot Returns the cotangent of a number.
curdate Curdate Returns the current date.
current_date CurrentDate Returns the current date.
current_time CurrentTime Returns the current time.
current_timestamp CurrentTimestamp Returns the current date and time.
current_user CurrentUser Returns the name of the current user.
currtime Currtime Returns the current time.
database Database Returns the current (default) database name.
date Date Returns the date portion of the specified datetime or date.
datediff Datediff Substracts the specified dates.
date_format DateFormat Formats the specified date with the specified format.
day Day Returns the day of the month (0-31).
DAYNAME DayName Returns the name of the day for the day portion of the argument, based on locale-name (if it is specified) or on the CURRENT LOCALE LC_TIME register value.
DAYOFWEEK DayOfWeek Returns the day of the week from a value, where 1 is Sunday and 7 is Saturday.
dayofyear DayOfYear Returns the day of the year from a value.
decode Decode Decrypts the encrypted string using the specified password string.
des_decrypt DesDecrypt Decrypts the string encrypted with the Triple-DES algorithm using the specified key.
des_encrypt DesEncrypt Encrypts the string with the specified key using the Triple-DES algorithm.
elt Elt Returns the element of the list of strings by the specified index.
encode Encode Encrypts the specified string using the specified password string.
encrypt Encrypt Encrypts the specified string using the Unix crypt() system call and returns a binary string.
exp Exp Returns the base of the natural logarithm (e) raised to a power specified by the argument.
field Field Returns the index (position) of the first string in the specified list of strings. Returns 0 if the first string is not found.
find_in_set FindInSet Returns the index (position) of the string in the specified substring list. The string list is specified as a string, in which the substrings are separated with commas.
floor Floor Return the smallest integer value that is not more than the specified value.
format Format Formats the specified number to a format like '#,###,###.##', rounded to the specified number of decimal places, and returns the result as a string.
found_rows FoundRows Returns the number of rows, that the previously executed SELECT statement would have returned if it had not included the LIMIT clause.
from_days FromDays Converts the specified day number to a date.
from_unixtime FromUnixtime Returns a representation of the specified unix_timestamp as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format (in the current timezone), depending on whether the function is used in a string or numeric context.
get_lock GetLock Tries to obtain a lock with the specified name, using a timeout of timeout seconds. A negative timeout value means infinite timeout. The lock is exclusive and cannot be held by more than one connection.
hex Hex Returns a hexadecimal representation of a value.
hour Hour Returns the hour for the specified time.
inet_aton InetAton Returns the numeric value of the specified IP address in network byte order (big endian).
inet_ntoa InetNtoa Returns the dotted-quad string representation of the address as a nonbinary string in the connection character set for the specified IP address in network byte order.
insert Insert Replaces a substring in the specified string at the specified position with the specified number of characters from the specified substring.
instr Instr Returns the position of the first occurrence of the specified substring in the specified string.
is_free_lock IsFreeLock Checks whether the specified lock is free for use.
is_used_lock IsUsedLock Checks whether the specified lock is locked.
last_day LastDay Returns a datetime value that represents the last day of the month of the argument.
last_insert_id LastInsertId Returns the value of an autoincrement column resulted from the most recent INSERT statement, if it was successful. If it was unsuccessful, returns an undefined value.
LCASE Lcase Returns the specified string in lowercase.
LEFT Left Returns the specified number of the leftmost characters from the specified string.
length Length Returns the length of the specified string in bytes.
ln Ln Returns the natural logarithm of a number.
LOAD_FILE LoadFile With one parameter, returns the natural logarithm of the specified value. When called with two parameters, returns the logarithm of the second specified value to the base (the first specified value).
log Log With one parameter, returns the natural logarithm of the specified value. When called with two parameters, returns the logarithm of the second specified value to the base (the first specified value).
log10 Log10 Return the common logarithm (base 10) of a number.
log2 Log2 Returns the logarithm of the specified value to the base 2.
lower Lower Returns a string in which all characters have been converted to lowercase characters using the rules from the Unicode standard associated with the specified locale.
ltrim Ltrim Removes blanks from the beginning of a string.
makedate Makedate Creates a date from the specified year and day of year.
maketime Maketime Creates a time from the specified hours, minutes, and seconds.
md5 Md5 Calculates MD5 checksum.
microsecond Microsecond Returns the microseconds from the specified argument.
mid Mid Return a substring of the specified string, starting from the specified position.
minute Minute Return the minute from the specified argument.
mod Mod Returns the remainder.
month Month Return the month part from the specified date.
monthname MonthName Returns a character string containing the name of the month (for example, January) for the month portion of expression,, based on locale-name (if it is specified) or on the CURRENT LOCALE LC_TIME register value.
now Now Returns the current date and time.
oct Oct Returns the string, containing the octal representation of a number.
old_password OldPassword Returns the hashed password from the specified plaintext password, using hashing techniques prior to MySQL 4.1.
ord Ord Returns the character code of the first character from the specified string.
password Password Returns the hashed password from the specified plaintext password.
period_add PeriodAdd Adds the specified period to the specified year-month.
period_diff PeriodDiff Returns the number of months between periods.
pi Pi Returns the Pi constant.
pow Pow Returns the specified number raised to the specified power.
quarter Quarter Returns an integer that represents the quarter of the year in which the specified date resides.
quote Quote Quotes and escapes the specified string for use in SQL.
radians Radians Returns the number of radians for the specified value in degrees.
rand Rand Returns a random number.
RELEASE_LOCK ReleaseLock Releases a lock with the specified name. Returns 1 if the lock was released, 0 if the lock was not established by this thread (in which case the lock is not released), and NULL if the named lock did not exist.
repeat Repeat Returns a character string composed of the first argument strings, repeated a specified by the second argument number times.
replace Replace Replaces occurrences of the specified substring in the specified string.
reverse Reverse Reverses the characters in the specified string.
right Right Returns the specified number of the rightmost characters from the specified string.
round Round Rounds the specified number.
rpad Rpad Returns a string that is padded on the right with the specified character, string, or with blanks.
rtrimRTRIM Rtrim Removes blanks from the end of a string.
schema Schema Returns the current (default) database name.
second Second Return the second from the specified argument.
sec_to_time SecToTime Converts the specified number of seconds to the 'HH:MM:SS' format.
session_user SessionUser The user name and host name provided by the client.
sha Sha Returns SHA-1 160-bit checksum.
sha1 Sha1 Returns SHA-1 160-bit checksum.
sign Sign Returns an indicator of the sign of an expression.
sin Sin Returns the sine of a number.
soundex Soundex Returns a 4-character code representing the sound of the words in the argument. This result can be compared with the sound of other strings.
space Space Returns a character string that consists of a specified number of blanks.
sqrt Sqrt Returns the square root of a number.
str_to_date StrToDate Converts the specified string to a date.
returns the start time of the current statement Subdate Subtracts the specified time value (interval) from the specified date.
substr Substr Returns a part of the specified string, starting from the specified position, of the specified length. If length is not specified, returns a portion till the end of the input string.
substring Substring Returns a part of the specified string, starting from the specified position, of the specified length. If length is not specified, returns a portion till the end of the input string.
substring_index SubstringIndex Returns the substring from the specified string before the specified number of occurrences of the delimiter.
subtime Subtime Subtracts the specified time values.
sysdate Sysdate Returns the time at which the function executes.
system_user SystemUser The user name and host name provided by the client.
time Time Returns the time portion of the specified datetime or time.
timediff Timediff Substracts the specified times.
time_format TimeFormat Formats the specified time with the specified format.
timestamp Timestamp Converts the specified date or datetime argument to a timestamp. If two arguments are specified, adds the second time argument to the first date or datetime.
time_to_sec TimeToSec Converts the specified time to the number of seconds.
to_days ToDayes Returns the number of days since year 0 for the specified date.
trim Trim Removes leading and trailing spaces from the specified string.
truncate Truncate Truncates the specified number to the specified count of decimal places.
ucase Ucase Returns a string in which all the characters have been converted to uppercase characters.
uncompress Uncompress Uncompresses the specified string, compressed by the COMPRESS() function.
uncompressed_length Uncompress Uncompresses the specified string, compressed by the COMPRESS() function.
unhex Unex Treats each pair of characters in the specified string as a hexadecimal number and converts it to a byte. Returns the result binary string of these bytes or Null if encunters non-hexadecimal digit.
unix_timestamp UnixTimestamp Returns a Unix timestamp.
upper Upper Returns a string in which all the characters have been converted to uppercase characters.
user User The user name and host name provided by the client.
utc_date UtcDate Returns the current date in UTC.
utc_time UtcTime Returns the current time in UTC.
utc_timestamp UtcTimestamp Returns the current date and time in UTC.
uuid Uuid Returns a Universal Unique Identifier (UUID).
version Version Returns MySQL server version as a string.
WEEK Week Returns the week of the year from a value.
weekday Weekday Returns the weekday index.
weekofyear Weekofyear Returns the calendar week of the date as a number in the range from 1 to 53.
year Year Returns the year of the specified date, in the range 1000 to 9999, or 0 for the "zero" date.
yearweek Yearweek Returns year and week for the specified date.

See Also

Entity Framework  | Entity Framework Canonical Functions