sqlsugar入门(2)-C#方法与sugar自定义函数的区别

1、使用tostring获取当天数据

            var list = ssc.Queryable<Student>().Where(o => o.CreateTime.Value.ToString("yyyy-MM-dd") == DateTime.Today.ToString("yyyy-MM-dd")).ToList();

            var sql = ssc.Queryable<Student>().Where(o => o.CreateTime.Value.ToString("yyyy-MM-dd") == DateTime.Today.ToString("yyyy-MM-dd")).ToSqlString();

看结果也是能查询到数据但是生成的sql语句为

SELECT [Id],[Name],[Version],[SchoolId],[Grade],[Age],[CreateTime],[Type],[IsDelete] FROM [Student] WHERE (DateName(Year,[CreateTime])+'-'+DateName(Month,[CreateTime])+'-'+DateName(Day,[CreateTime]) =DateName(Year,'2020/10/11 0:00:00')+'-'+DateName(Month,'2020/10/11 0:00:00')+'-'+DateName(Day,'2020/10/11 0:00:00'))

和实际需求还是有点差异。

2、使用sugar内置函数获取当天数据

            var list1 = ssc.Queryable<Student>().Where(o=>SqlFunc.DateIsSame(o.CreateTime,DateTime.Today)).ToList();

            var sql1 = ssc.Queryable<Student>().Where(o => SqlFunc.DateIsSame(o.CreateTime, DateTime.Today)).ToSqlString();

生成的sql语句为

SELECT [Id],[Name],[Version],[SchoolId],[Grade],[Age],[CreateTime],[Type],[IsDelete] FROM [Student] WHERE (DATEDIFF(day,[CreateTime],'2020/10/11 0:00:00')=0)

3、自定义扩展函数获取当天数据

添加静态扩展方法

public static DateTime ToDate111<T>(this T str)

        {

            throw new NotSupportedException("Can only be used in expressions");

        }

添加扩展方法，解析为语句的时候注意一下，字段名称和值

            expMethods.Add(new SqlFuncExternal()

            {

                UniqueMethodName = "ToDate111",

                MethodValue = (expInfo, dbType, expContext) =>

                {

                    if (dbType == DbType.SqlServer)

                        if (expInfo.Args.Count == 0)

                        {

                            return string.Format(" getdate() ");

                        }

                        else

                        {

                            return string.Format(" cast (CONVERT (varchar ( 100), {0} , 111) as date) ", expInfo.Args[0].MemberName);

                        }

                    if (dbType == DbType.MySql)

                        if (expInfo.Args.Count == 0)

                        {

                            return string.Format(" getdate() ");

                        }

                        else

                        {

                            return string.Format(" date_format( {0}, '%Y/%m/%d' ) ", expInfo.Args[0].MemberName);

                        }

                    else

                        throw new Exception("未实现");

                }

            });

生成的sql语句为

SELECT [Id],[Name],[Version],[SchoolId],[Grade],[Age],[CreateTime],[Type],[IsDelete] FROM [Student] WHERE (cast (CONVERT (varchar ( 100), [CreateTime] , 111) as date) = '2020/10/11 0:00:00' )

4、自行扩展ToString("yyyy-MM-dd")功能

Demo地址：https://gitee.com/xuanyun2018/sqlsugardemo.git

下一章节修改源码看是否能支持原生ToString("yyyy-MM-dd")解析功能。

巴特西