EFCore调优 EFCore原生sql语句

潘诺西亚的火山

已于 2022-06-24 12:00:14 修改

阅读量299

点赞数

文章标签：数据库 microsoft sql

于 2022-06-11 00:54:21 首次发布

本文链接：https://blog.csdn.net/helldoger/article/details/125230281

版权

这篇博客展示了如何在C#中使用Entity Framework Core（EF Core）来配置数据库上下文、读取配置文件、初始化数据以及进行SQL查询。示例中涉及了Customers和Orders表的一对多关系，并通过LINQ查询找到未下单的客户。此外，还演示了原生ADO.NET Core的查询方式。

摘要由CSDN通过智能技术生成

初始化数据：

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCoreDemon1
{
    internal class MyDbContext:DbContext
    {
        public DbSet<Article> Articles { get; set; } //API中的名字
        public DbSet<Comment> Comments { get; set; }
        public DbSet<Comment2> Comment2s { get; set; }
        public DbSet<Customers> Customers { get; set; } //API中的名字
        public DbSet<Orders> Orders { get; set; }

        private ConfigurationBuilder cfgBuilder = new ConfigurationBuilder();


        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            //Microsoft.Extensions.Configuration.JsonConfigurationExtensions
            cfgBuilder.AddJsonFile("DbCfg.json", optional: true, reloadOnChange: true);
            IConfigurationRoot configRoot = cfgBuilder.Build();
            string connString = configRoot.GetSection("ConnectionStrings:SqliteConnectionString").Value;
            optionsBuilder.UseSqlite(connString);
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Customers>()
                .HasData
                (
                new Customers { Id = 1, Name = "Joe" },
                new Customers { Id = 2, Name = "Henry" },
                new Customers { Id = 3, Name = "Sam" },
                new Customers { Id = 4, Name = "Max" }
                );
            modelBuilder.Entity<Orders>()
                .HasData
                (
                new Orders { Id = 1, Name = "DDD", CustomerId = 3, CustomerId2 = 3 },
                new Orders { Id = 2, Name = "EEE", CustomerId = 1, CustomerId2 = 1 },
                new Orders { Id = 3, Name = "FFF", CustomerId = 2 },
                new Orders { Id = 4, Name = "GGG", CustomerId = 4 }
                );
            modelBuilder.Entity<Comment2>()
                .HasData
                (
                new Comment2 { Id = 1, Message = "Orders1的评论", OrdersId = 1 },
                new Comment2 { Id = 2, Message = "Orders1的评论", OrdersId = 1 },
                new Comment2 { Id = 3, Message = "Orders2的评论", OrdersId = 2 },
                new Comment2 { Id = 4, Message = "Orders2的评论", OrdersId = 2 },
                new Comment2 { Id = 5, Message = "Orders3的评论", OrdersId = 3 },
                new Comment2 { Id = 6, Message = "Orders3的评论", OrdersId = 3 },
                new Comment2 { Id = 7, Message = "Orders3的评论", OrdersId = 3 },
                new Comment2 { Id = 8, Message = "Orders4的评论", OrdersId = 4 }
                );
            modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
        }
    }
}

json配置

{
  "ConnectionStrings": {
    "SqliteConnectionString": "Data Source=D:\\Db\\DbSqlite.db",
    "MySQLConnectionString": "server=127.0.0.1; database=OneToMany; uid=root; pwd=123456;"
  }
}

结果：

// See https://aka.ms/new-console-template for more information
global using EFCoreDemon1;
global using EFCoreDemon1.Entities;
global using Microsoft.EntityFrameworkCore;
global using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System.Data.Common;


//某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

using MyDbContext myDb = new MyDbContext();

var customers = myDb.Orders.Where(o=>o.CustomerId2 == null).Select(o=>o.Customers);

//第一次Select 通过外键指向集合，从进入Orders另一张表Customers，第二个select 使用select选择指定字段
var name = myDb.Orders.Where(o => o.CustomerId2 == null).Select(o => o.Customers).Select(c => c.Name);

//第一次Select 通过外键指向集合，从Customers进入另一张表Orders，第二个select，从Orders进入表Comment2
var test = myDb.Customers.Where(c => c.Id > 1).Select(c => c.Orders).Where(o => o.Name == "DDD").Select(o => o.Comment2s);
//var a = myDb.Customers.FromSqlInterpolated($"Select * from Orders").ToList();
//myDb.Orders.FromSqlInterpolated($"Select Name From Customers where Customers.Id not in ()")

foreach (var item in customers)
{
    Console.WriteLine(item.Name);
}
Console.WriteLine("=======================");
foreach (var item in name)
{
    Console.WriteLine(item);
}
Console.WriteLine("=======================");

//最后两个表的关系是一对多，必须两次循环
foreach (var item in test)
{
    foreach (var i in item)
    {
        Console.WriteLine(i.Message);
    }
}

/*
 结果
 Henry
Max
=======================
Henry
Max
=======================
Orders1的评论
Orders1的评论
 */


//写原生 AdoNetCore
Console.WriteLine("=======================");
DbConnection conn = myDb.Database.GetDbConnection();

if (conn.State != System.Data.ConnectionState.Open)
{
    await conn.OpenAsync();
}

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "select Customers.name from Customers where Customers.Id in (select CustomerId2 from Orders)";
    using(var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            var test1 = reader.GetValue(0);
            Console.WriteLine(test1);
        }
    }
}

/*
 结果：
 Joe
Sam
 */

实体类和配置：

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

namespace EFCoreDemon1.Entities
{
    public class Customers
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Orders Orders { get; set; }
    }
}

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

namespace EFCoreDemon1.Entities
{
    public class Orders
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Customers Customers { get; set; }
        public int CustomerId { get; set; }
        public int? CustomerId2 { get; set; }
        public List<Comment2> Comment2s { get; set; }
    }
}

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

namespace EFCoreDemon1.Entities
{
    public class Comment2
    {
        public int Id { get; set; }
        public Orders Orders { get; set; }
        public int OrdersId { get; set; } //外键
        public string Message { get; set; }
    }
}

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

namespace EFCoreDemon1.Db
{
    public class CustomersConfig : IEntityTypeConfiguration<Customers>
    {
        public void Configure(EntityTypeBuilder<Customers> builder)
        {
            builder.ToTable("Customers");
            builder.HasKey(c => c.Id); //设置主键
        }
    }
}

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

namespace EFCoreDemon1.Db
{
    public class OrdersConfig : IEntityTypeConfiguration<Orders>
    {
        public void Configure(EntityTypeBuilder<Orders> builder)
        {
            builder.ToTable("Orders");
            builder.HasKey(a => a.Id); //设置主键
            builder.HasOne<Customers>(o => o.Customers)
                .WithOne(c => c.Orders).HasForeignKey<Orders>(o => o.CustomerId);
        }
    }
}

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

namespace EFCoreDemon1.Db
{
    public class Comment2Config : IEntityTypeConfiguration<Comment2>
    {
        public void Configure(EntityTypeBuilder<Comment2> builder)
        {
            builder.ToTable("Comment2");
            builder.HasKey(a => a.Id); //设置主键
            builder.HasOne<Orders>(c => c.Orders)
                .WithMany(o => o.Comment2s)
                .HasForeignKey(o => o.OrdersId);
        }
    }
}

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

	<ItemGroup>
		<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.3">
			<PrivateAssets>all</PrivateAssets>
			<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
		</PackageReference>
		<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.3">
			<PrivateAssets>all</PrivateAssets>
			<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
		</PackageReference>
		<PackageReference Include="Microsoft.Extensions.Configuration" Version="6.0.1" />
		<PackageReference Include="Microsoft.Extensions.Configuration.Binder" Version="6.0.0" />
		<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="6.0.0" />
		<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="6.0.0" />
		<PackageReference Include="Microsoft.Extensions.Options" Version="6.0.0" />
		<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
		<PackageReference Include="NModbus4.NetCore" Version="2.0.1" />
		<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.1" />
	</ItemGroup>

	<ItemGroup>
	  <None Update="DbCfg.json">
	    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
	  </None>
	</ItemGroup>

	<ItemGroup>
	  <Folder Include="Migrations\" />
	</ItemGroup>

</Project>