[大数据]用sqlsugar在不定义外键的情况下实现导航查询

定义sqlsugar的helper

使用文档:

https://www.donet5.com/Home/Doc?typeId=1206

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sugarTest.Provider
{
    public class SqlSugarHelper
    {
        public static string ConnectionString = string.Empty; //必填, 数据库连接字符串
        public static SqlSugarClient db
        {
            get => new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = ConnectionString,
                DbType = DbType.Sqlite,         //必填, 数据库类型
                IsAutoCloseConnection = true,       //默认false, 时候知道关闭数据库连接, 设置为true无需使用using或者Close操作
                InitKeyType = InitKeyType.Attribute    //默认SystemTable, codefist需要使用Attribute
            });
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sugarTest.Provider
{
    public class SqlSugarService
    {
        /// <summary>
        /// 设置连接字符串
        /// </summary>
        /// <param name="ConnectionStr"></param>
        public static void SetConnectionStr(string ConnectionStr)
        {
            SqlSugarHelper.ConnectionString = ConnectionStr;
        }
    }
}

定义两个实体类

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sugarTest.Model
{
    public class Order
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Descript { get; set; }

        //在SqlSugar中导航属性需要配置为忽略，避免被直接解析为SQL，否则会提示Sequence contains no elements
        [SugarColumn(IsIgnore = true)] 
        public List<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
}
}

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sugarTest.Model
{
    public class OrderItem
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string ItemCode { get; set; }
        public decimal Pirce { get; set; }

        //外键, 需要自己指定, 使用关键词First()
        public int OrderId { get; set; }

        //在SqlSugar中导航属性需要配置为忽略，避免被直接解析为SQL，否则会提示Sequence contains no elements
        [SugarColumn(IsIgnore = true)] 
        public Order Order { get; set; }
    }
}

实现一对多查询

using sugarTest.Model;
using sugarTest.Provider;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace sugarTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.Load += Form1_Load;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlSugarService.SetConnectionStr("Data Source=" + Application.StartupPath + str2);
            //this.DGV.AutoGenerateColumns = false;
            //this.DGV.Columns[0].FillWeight = 25;
            //this.DGV.Columns[1].FillWeight = 25;
            //this.DGV.Columns[2].FillWeight = 25;
            //this.DGV.Columns[3].FillWeight = 25;
        }
        
        string str2 = "\\Config\\Test.db;Pooling=true;FailIfMissing=false";

        private void btnQuery_Click(object sender, EventArgs e)
        {
            SugarResponsitory sugarResponsitory = new SugarResponsitory();
            //var list = sugarResponsitory.Db.Queryable<Order>().ToList();

            //多表查询
            //需要注意的是，这两个是固定写法。
            //其中，一对一要求必须从主对象开始查询。所谓主对象就是必须持有一个外键指向另一个表。
            //一对多要求从拥有集合属性的那段（也就是其中的“一”）开始，关联指示为 集合.First().外键 。
            //还有一点就是SqlSugar的导航属性必须手动加载，不会自动加载进来，所以完全不会出现深度递归的问题。
            var list = sugarResponsitory.Db.Queryable<Order>().
                Mapper(it => it.OrderItems, it => it.OrderItems.First().OrderId).Where(it=>it.Id>2).ToList();

            //如果 Order 中定义的是 public OrderItem OrderItem { get; set; } 则为一对一查询
            //var list = sugarResponsitory.Db.Queryable<Order>().
            //    Mapper(it => it.OrderItem, it => it.OrderItem.First().OrderId).Where(it => it.Id > 2).ToList();

            this.DGV.DataSource = list;
        }

        //直接运行 没有数据库也会建立Test.db
        private void btnCreateDataBase_Click(object sender, EventArgs e)
        {
            SugarResponsitory sugarResponsitory = new SugarResponsitory();
            //sugarResponsitory.db.DbMaintenance.CreateDatabase();
            sugarResponsitory.Db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(OrderItem));
            sugarResponsitory.Db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order));
            //三种重载
            //void InitTables(string entitiesNamespace);
            //void InitTables(string[] entitiesNamespaces);
            //void InitTables(params Type[] entityTypes);
        }
    }
}