LambdaToSql 发布 兰姆达转换sql

文档目录索引

1. 查询、函数、分组、排序、分页
2. 添加 Insert into
3. 编辑 Update set
4. 删除 Delete
5. 生成实体
6. 内置常用工具类库  文档完善中...
7. 事务处理
8. Join 连接查询 1.0.2.X版本中添加 开发中...

开源地址:https://gitee.com/wangshuyu/LambdaToSql

简介：

LambdaToSql 简单的兰姆达转换sql，可以直接.ToList()执行，返回查询结果

第一个版本支持：

1. Where 条件查询
2. Order by 排序
3. Group by 分组
4. 简单分页，只支持row_number
5. 支持数据库函数：Avg Max Min Sum Count
6. 不需要任何第三方类库

使用前置条件：

1. 主键必须是 Guid  uniqueidentifier  
2. 表必须有 CreateTime datetime ，给分页排序使用，后期会把这个条件处理掉

第一个版不支持：

1. 暂不支持事物处理
2. 暂不支持多表join查询
3. 只支持Microsoft SQL Server数据库,分页不支持SQL Server 2000

测试表Sql语句：

CREATE TABLE [dbo].[System_UserInfo](
    [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_UserInfo_Guid]  DEFAULT (newid()),
    [LoginName] [nvarchar](32) NULL,
    [UserName] [nvarchar](32) NULL,
    [PassWord] [nvarchar](64) NULL,
    [Cert] [uniqueidentifier] NULL,
    [IdCard] [nvarchar](64) NULL,
    [Birthday] [nvarchar](64) NULL,
    [Address] [nvarchar](1024) NULL,
    [ImgUrl] [nvarchar](128) NULL,
    [Gender] [nvarchar](2) NULL,
    [Mobile] [nvarchar](16) NULL,
    [Remark] [nvarchar](1024) NULL,
    [Unionid] [nvarchar](128) NULL,
    [CreateTime] [datetime] NULL CONSTRAINT [DF_UserInfo_CreaTime]  DEFAULT (getdate()),
    [IsDelete] [int] NULL,
    [CreateUserInfoID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_USER] PRIMARY KEY NONCLUSTERED
(
    [Guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Guid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'LoginName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'UserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'PassWord'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'证件类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Cert'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'IdCard'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Birthday'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Address'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'头像' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'ImgUrl'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Gender'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手机号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Mobile'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'微信登录唯一码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'Unionid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'CreateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否删除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'IsDelete'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo', @level2type=N'COLUMN',@level2name=N'CreateUserInfoID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'System_UserInfo'
GO

测试实体：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using LambdaToSql;
using LambdaToSql.FrameWork;
using LambdaToSql.Extended;

namespace EntityModel
{

    /// <summary>
    /// 用户表
    /// </summary>
    [Serializable]
    [DataContract]
    public class System_UserInfo
    {

        /// <summary>
        /// 用户ID
        /// </summary>
        [DataMember]
        public System.Guid Guid { get; set; }

        /// <summary>
        /// 登录名称
        /// </summary>
        [DataMember]
        public string LoginName { get; set; }

        /// <summary>
        /// 用户名
        /// </summary>
        [DataMember]
        public string UserName { get; set; }

        /// <summary>
        /// 密码
        /// </summary>
        [DataMember]
        public string PassWord { get; set; }

        /// <summary>
        /// 证件类型
        /// </summary>
        [DataMember]
        public Nullable<System.Guid> Cert { get; set; }

        /// <summary>
        /// 身份证号
        /// </summary>
        [DataMember]
        public string IdCard { get; set; }

        /// <summary>
        /// 出生日期
        /// </summary>
        [DataMember]
        public string Birthday { get; set; }

        /// <summary>
        /// 地址
        /// </summary>
        [DataMember]
        public string Address { get; set; }

        /// <summary>
        /// 头像
        /// </summary>
        [DataMember]
        public string ImgUrl { get; set; }

        /// <summary>
        /// 性别
        /// </summary>
        [DataMember]
        public string Gender { get; set; }

        /// <summary>
        /// 手机号
        /// </summary>
        [DataMember]
        public string Mobile { get; set; }

        /// <summary>
        /// 备注
        /// </summary>
        [DataMember]
        public string Remark { get; set; }

        /// <summary>
        /// 微信登录唯一码
        /// </summary>
        [DataMember]
        public string Unionid { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        [DataMember]
        public Nullable<System.DateTime> CreateTime { get; set; }

        /// <summary>
        /// 是否删除
        /// </summary>
        [DataMember]
        public Nullable<int> IsDelete { get; set; }

        /// <summary>
        /// 创建人ID
        /// </summary>
        [DataMember]
        public Nullable<System.Guid> CreateUserInfoID { get; set; }
    }
}

测试代码，具体查看后续文章：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LambdaToSql.FrameWork;

namespace Web
{
    public partial class Clent : System.Web.UI.Page
    {
        LambdaToSql.SqlClient DB = new LambdaToSql.SqlClient();

        protected void Page_Load(object sender, EventArgs e)
        {
            //生成实体对象文件
            //LambdaToSql.SysConfig.Init();
            //LambdaToSql.Fireworks.CreateEntity.Init("d:/class/");

            //Find查询主键
            //var obj = db.QueryTable<EntityModel.System_UserInfo>().Find(Guid.Parse("9c1d1247-cd2c-424d-be14-ef8abae655a4"));

            //var item = new string[] { "15842709668", "18609877687", "15804279711", "13998756128", "13019965033" }.ToList();
            //var list = db.QueryTable<EntityModel.System_UserInfo>(ex => LambdaToSql.Fireworks.Method.In(ex.LoginName, item)).ToList();
            //var list = db.QueryTable<EntityModel.System_UserInfo>(ex => item.Contains(ex.LoginName)).ToList();

            function();
            Select();
        }

        private void function() //函数查询
        {
            var list = DB.QueryTable<EntityModel.System_UserInfo>()
                //.Where(ex => ex.LoginName.Contains("15"))
                //.Where(ex => ex.LoginName.StartsWith("158"))
                //.Where(ex => ex.LoginName.EndsWith("88"))
                //.Where(ex => ex.LoginName.NotContains("88"))
                //.Where(ex => ex.LoginName == "Shuyu".ToLower())
                // .Where(ex => item.Contains(ex.LoginName))
                //.Where(ex => item.NotContains(ex.LoginName))
                .ToList();
        }

        private void Select()//基本查询 分页
        {
            int total = ;
            var list = DB.QueryTable<EntityModel.System_UserInfo>()
                    //.Select(ex => new { ex.LoginName, ex.UserName })
                    //.Select(ex => new { ex.PassWord, ex.IdCard })
                    //.Where(ex => ex.LoginName == "test" || ex.UserName == "社区民警")
                    //.Where(ex => ex.LoginName == "test")
                    //.GroupBy(ex => new { ex.LoginName })
                    //.GroupBy(ex => new { ex.UserName })
                    //.OrderByDescending(ex => ex.LoginName)
                    //.OrderBy(ex => ex.UserName)
                    //.Take(15)
                    //.Where(ex => ex.LoginName.Contains("15"))
                    //.Where(ex => ex.LoginName.StartsWith("158"))
                    //.Where(ex => ex.LoginName.EndsWith("88"))
                    //.Where(ex => ex.LoginName.NotContains("88"))
                    //.Where(ex => ex.LoginName == "Shuyu".ToLower())

                    //.Where(ex => Method.Like(ex.LoginName, "158%"))
                    //.Where(ex => Method.NotLike(ex.LoginName, "158%"))

                    //.Where(ex => Method.In(ex.LoginName, "15841479211"))
                    //.Where(ex => Method.In(ex.LoginName, item))
                    //.Where(ex => Method.NotIn(ex.LoginName, item))

                    //.Count();
                    //.Take(10)
                    //.Skip(2)
                    //.OrderBy(ex => ex.CreateTime)
                    //.Sum(ex => ex.LogOnCount);
                    //.Min(ex => ex.LogOnCount);
                    //.Max(ex => ex.LogOnCount);
                    //.Avg(ex => ex.LogOnCount);
                    //.First();
                    //.FirstOrDefault();
                    //.ToPageList(3, 15, ref total);
                    .ToList();
        }
    }
}

Web.config配置文件

<connectionStrings>
   <add name="ConnectionString" connectionString="Server=.;Database=Test;User ID=sa;Password=123456" providerName="System.Data.SqlClient" />
</connectionStrings>

性能：

1. 基本和Ado.Net原生接近,lambda解析转换成sql语句，然后使用DBHelper参数化查询的数据库，性能损耗是在兰姆达转语句上，后续版本会加缓存机制。
2. 暂时不支持事物处理
3. 接口 命名重新规划，但还是不太好，后续版本会慢慢规划，暂时预计大概发布 10个版本，出一个稳定版本
4. 稳定后会开源出来 哈哈

如何获取：

1. NuGet管理器 搜索：LambdaToSql
2. Install-Package LambdaToSql -Version 版本号
3. 

使用示例：

1. 配置文件中添加链接数据库字符串  <add name="ConnectionString" connectionString="Server=.;Database=test;User ID=sa;Password=123456" providerName="System.Data.SqlClient" />
2. 

3. 使用实例

   DB.QueryTable<实体对象>()
   .Where(条件)
   .Select(字段)
   .GroupBy(分组)
   .OrderByDescending(排序)
   .OrderBy(排序)
   .Skip(页码)
   .Take(数量)
   .ToList();//返回集合

   .Count();
   .Sum(求和);
   .Min(最小值);
   .Max(最大值);
   .Avg(平均值);
   .First();//第一条
   .FirstOrDefault();//第一条
   .ToPageList(页码, 每页数量, ref 总数);//分页

4. 其它

最后感谢:SqlSugar 作者 开源