public void CurrentUserTest()
{
using (var edm = new NorthwindEntities())
{
var a = from c in edm.Customers
select new{user=SqlFunctions.CurrentUser()};
Console.WriteLine(a.FirstOrDefault().user);//dbo
//CURRENT_USER AS [C2]
a.TraceSql();
}
}
public void DataLengthTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.DataLength("zxm");
// CAST(DATALENGTH(N'zxm') AS int) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First().Value);//6
}
}
public void CurrentTimeTest()
{
using (var edm = new NorthwindEntities())
{
var a = from c in edm.Customers
select new { time = SqlFunctions.CurrentTimestamp() };
Console.WriteLine(a.FirstOrDefault().time);//2013/1/22 14:09:13
//CURRENT_TIMESTAMP AS [C2]
a.TraceSql();
}
}
public void DateDiffTest1()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.DateDiff("Day",new DateTime(2012,12,24),DateTime.Now);
//DATEDIFF(Day, convert(datetime2, '2012-12-24 00:00:00.0000000', 121), SysDateTime()) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First().Value);//29
}
}
public void DateAddTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.DateAdd("Year",1,DateTime.Now);
//DATEADD(Year, cast(1 as float(53)), SysDateTime()) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First().Value);//2014/1/22 14:08:32
}
}
public void test3()
{
var arr1 = BitConverter.GetBytes(123);
using (var edm = new NorthwindEntities())
{
var cust1 = edm.Employees
.Where(c => SqlFunctions.CharIndex(arr1, c.Photo, (int?)0) > -1);
//WHERE ( CAST(CHARINDEX(@p__linq__0, [Extent1].[Photo], 0) AS int)) > -1
cust1.TraceSql();
Console.WriteLine(cust1.Count());
}
}
public void test4()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Employees
where SqlFunctions.Checksum("zxm", "zxm", e.City) == -1555502783
select e;
//WHERE -1555502783 = (CHECKSUM(N'zxm', N'zxm', [Extent1].[City]))
cust1.TraceSql();
Console.WriteLine(cust1.Count());
}
}
public void test3()
{
var dt1 = DateTime.Now;
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Employees
where SqlFunctions.Checksum(dt1, dt1, e.BirthDate) <= 524209
select e;
//WHERE (CHECKSUM(@p__linq__0, @p__linq__1, [Extent1].[BirthDate])) <= 524209
cust1.TraceSql();
Console.WriteLine();
}
}
public void test2()
{
var arr1 = BitConverter.GetBytes(123);
var arr2 = arr1;
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Employees
where SqlFunctions.Checksum(arr1, arr2, e.Photo) > 0
select e;
//WHERE (CHECKSUM(@p__linq__0, @p__linq__1, [Extent1].[Photo])) >0
cust1.TraceSql();
Console.WriteLine();
}
}
public void test1()
{
Guid? guid1 = new Guid("4A83D44A-5612-4E78-89EE-08F894912C0A");
Guid? guid2 = new Guid("4A83D44A-5612-4E78-89EE-08F894912C0A");
Guid? guid3 = new Guid("4A83D44A-5612-4E78-89EE-08F894912C0A");
using (var edm = new NorthwindEntities())
{
var cust1 =from c in edm.Customers
where SqlFunctions.Checksum(guid1,guid2,guid3)>=-1
select c;
//WHERE (CHECKSUM(@p__linq__0, @p__linq__1, @p__linq__2)) >= -1
cust1.TraceSql();
Console.WriteLine();
}
}
public void test4()
{
using (var edm = new NorthwindEntities())
{
var cust1 = edm.Customers
.Where(c => SqlFunctions.CharIndex(c.City,"London", (int?)1)==1);
//WHERE 1 = ( CAST(CHARINDEX([Extent1].[City], N'London', 1) AS int))
cust1.TraceSql();
Console.WriteLine(cust1.Count());
//cust1 = edm.Customers
// .Where(c =>c.City.IndexOf("London",0)==0);
//WHERE 1 = ( CAST(CHARINDEX([Extent1].[City], N'London', 1) AS int))
//cust1.TraceSql();
//Console.WriteLine(cust1.Count());
}
}
public void test2()
{
using (var edm = new NorthwindEntities())
{
var cust1 = edm.Customers
.Where(c => SqlFunctions.CharIndex("don",c.City,(long?)0) ==4);
//WHERE 4 = ( CAST(CHARINDEX(N'don', [Extent1].[City], cast(0 as bigint)) AS bigint))
cust1.TraceSql();
Console.WriteLine(cust1.Count());
//cust1 = edm.Customers
// .Where(c => c.City.IndexOf("don", 0) == 3);
////WHERE 4 = ( CAST(CHARINDEX(N'don', [Extent1].[City], cast(0 as bigint)) AS bigint))
//cust1.TraceSql();
//Console.WriteLine(cust1.Count());
}
}
public void HostNameTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.HostName();
//HOST_NAME() AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//ZXM-PC
}
}
public void IsNumericTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.IsNumeric("2012-03-45");
//ISNUMERIC(N'2012-03-45') AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//0
}
}
public void NCharTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.NChar(98);
//NCHAR(98) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//98
}
}
public void UserNameTest2()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.UserName(2);
//USER_NAME(2) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//guest
}
}
public void UnicodeTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.Unicode("ELinq");
//UNICODE(N'ELinq') AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//69
}
}
public void RandTest2()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.Rand();
//RAND(180) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());
}
}
public void ExpTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.Exp(1M);
//EXP(1) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//2.71828182845905
}
}
public void SquareTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.Square(1.2);
//SQUARE(cast(1.2 as float(53))) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//1.44
}
}
public void SquareRootTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.SquareRoot(2.25);
//SQRT(cast(2.25 as float(53))) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//1.5
}
}
public void PatIndexTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.PatIndex("xx-xx",p.ProductName);
// CAST(PATINDEX(N'xx-xx', [Extent1].[ProductName]) AS int) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//0
}
}
public void PiTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.Pi();
// PI() AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//3.14159265358979
}
}
public void SoundCodeTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select new{str1=SqlFunctions.SoundCode("too"),str2=SqlFunctions.SoundCode("two")};
//SOUNDEX(N'too') AS [C2], SOUNDEX(N'two') AS [C3]
cust1.TraceSql();
Console.WriteLine(cust1.First());//{ str1 = T000, str2 = T000 }
}
}
public void ReplicateTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.Replicate("Elinq",3);
//REPLICATE(N'Elinq', 3) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//ElinqElinqElinq
}
}
public void test6()
{
using (var edm = new NorthwindEntities())
{
var cust1 = edm.Customers
.Where(c => SqlFunctions.CharIndex("Lond", c.City) ==1);
Assert.IsNotNull(cust1);
Console.WriteLine(cust1.Count());
//WHERE 1 = ( CAST(CHARINDEX(N'Lond', [Extent1].[City]) AS int))
cust1.TraceSql();
cust1 = edm.Customers
.Where(c => c.City.IndexOf("Lond") ==0);
//WHERE 0 = (( CAST(CHARINDEX(N'Lond', [Extent1].[City]) AS int)) - 1)
cust1.TraceSql();
Console.WriteLine(cust1.Count());
}
}
public void StringConvertTest3()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.StringConvert(2.25, 4,2);
//SQRT(cast(2.25 as float(53)),4,3) AS [C1]
cust1.TraceSql();
Console.WriteLine("*" + cust1.First() + "*");//*2.25*
}
}
public void StuffTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.Stuff("ELinq", 1, 2, "NLite.Data");
//STUFF(N'ELinq', 1, 2, N'NLite.Data') AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//NLite.Datainq
}
}
public void SpaceTest()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select new{newStr="EL"+SqlFunctions.Space(2)+"inq"};
//N'EL' + SPACE(2) + N'inq' AS [C2]
cust1.TraceSql();
Console.WriteLine(cust1.First().newStr);//EL inq
}
}
public void QuoteNameTest2()
{
using (var edm = new NorthwindEntities())
{
var cust1 = from p in edm.Products
select SqlFunctions.QuoteName("LinqToEntity","'");
//QUOTENAME(N'LinqToEntity', N'''') AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First());//'LinqToEntity'
}
}
