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' } }