dotnet efcore 多数据库 使用

ef core 的使用

依赖包

efcore-multi-db/MultiDb.Two/MultiDb.Two.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite.Core" Version="7.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.3" />
    <PackageReference Include="microsoft.entityframeworkcore.sqlserver" Version="7.0.15" />
    <PackageReference Include="microsoft.entityframeworkcore.tools" Version="7.0.15" />
  </ItemGroup>
</Project>

数据库上下文配置文件

efcore-multi-db/MultiDb.Two/DbTwoContext.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.Two
{
    public class DbTwoContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<UserLecture> UserLectures { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                //不同的数据库,只需要修改此处的连接字符串即可
                // optionsBuilder.UseSqlServer(@"Server=(localdb)\ProjectsV13;Database=DbTwo;Integrated Security=True;MultipleActiveResultSets=True;");
                optionsBuilder.UseSqlite(@"Data Source=DbTwo.db");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>().ToTable("User");
            modelBuilder.Entity<UserLecture>().ToTable("UserLecture");
        }
    }
}

数据库实体

efcore-multi-db/MultiDb.Two/User.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.Two
{
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

> `efcore-multi-db/MultiDb.Two/UserLecture.cs`

```cs
using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.Two
{
    public class UserLecture
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public int LectureId { get; set; }

        public User User { get; set; }
    }
}

数据库2和数据库1项目结构类似

efcore-multi-db/MultiDb.One/MultiDb.One.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite.Core" Version="7.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.3" />
    <PackageReference Include="microsoft.entityframeworkcore.sqlserver" Version="7.0.15" />
    <PackageReference Include="microsoft.entityframeworkcore.tools" Version="7.0.15" />
  </ItemGroup>
</Project>

efcore-multi-db/MultiDb.One/Lecture.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.One
{
    public class Lecture
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public string Name { get; set; }
    }
}

efcore-multi-db/MultiDb.One/DbOneContext.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.One
{
    public class DbOneContext : DbContext
    {
        public DbSet<Lecture> Lectures { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                // optionsBuilder.UseSqlServer(@"Server=(localdb)\ProjectsV13;Database=DbOne;Integrated Security=True;MultipleActiveResultSets=True;");
                optionsBuilder.UseSqlite(@"Data Source=DbOne.db");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Lecture>().ToTable("Lecture");
        }
    }
}

主程序

添加两个子项目的引用

efcore-multi-db/MultiDb.App/MultiDb.App.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <ProjectReference Include="..\MultiDb.One\MultiDb.One.csproj" />
    <ProjectReference Include="..\MultiDb.Two\MultiDb.Two.csproj" />
  </ItemGroup>
</Project>

创建model

model > entity,model的范围要大于entity,包含了数据表之间的关系,如果没有关系,两者差不多,比如下UserModel表示就是User和UserLecture之间的一对多关系.

efcore-multi-db/MultiDb.App/UserModel.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.App
{
    public class UserModel
    {
        public int id { get; set; }
        public string name { get; set; }

        public List<LectureModel> lectures { get; set; }
    }
}

efcore-multi-db/MultiDb.App/DbInitializer.cs

using MultiDb.One;
using MultiDb.Two;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MultiDb.App
{
    public static class DbInitializer
    {
        public static void Initialize(DbOneContext db1, DbTwoContext db2)
        {            
            db1.Database.EnsureCreated();
            db2.Database.EnsureCreated();

            if (!db2.Users.Any() && !db1.Lectures.Any())
            {
                var users = new User[]
                {
                    new User { Name = "User A" },
                    new User { Name = "User B" },
                    new User { Name = "User C" }
                };

                db2.Users.AddRange(users);
                db2.SaveChanges();

                var lectures = new Lecture[]
                {
                    new Lecture { Name = "Lecture A", UserId = 1 },
                    new Lecture { Name = "Lecture B", UserId = 2 },
                    new Lecture { Name = "Lecture C", UserId = 3 }
                };

                db1.Lectures.AddRange(lectures);
                db1.SaveChanges();

                var userLectures = new UserLecture[]
                {
                    new UserLecture { LectureId = 1, UserId = 2 },
                    new UserLecture { LectureId = 1, UserId = 3 },
                    new UserLecture { LectureId = 2, UserId = 1 },
                    new UserLecture { LectureId = 2, UserId = 3 },
                    new UserLecture { LectureId = 3, UserId = 1 },
                    new UserLecture { LectureId = 3, UserId = 2 }
                };

                db2.UserLectures.AddRange(userLectures);
                db2.SaveChanges();
            }
        }
    }
}

efcore-multi-db/MultiDb.App/LectureModel.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace MultiDb.App
{
    public class LectureModel
    {
        public int id { get; set; }
        public string name { get; set; }
        public UserModel proctor { get; set; }

        public List<UserModel> attendees { get; set; }
    }
}

efcore-multi-db/MultiDb.App/DataExtensions.cs

using MultiDb.One;
using MultiDb.Two;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MultiDb.App
{
    public static class DataExtensions
    {
        public static List<LectureModel> GetLectures(this DbOneContext db1, DbTwoContext db2)
        {
            var model = db1.Lectures.Select(x => new LectureModel
            {
                id = x.Id,
                name = x.Name,
                proctor = db2.GetUser(x.UserId),
                attendees = db2.GetAttendees(x.Id)
            }).OrderBy(x => x.name).ToList();

            return model;
        }

        public static UserModel GetUser(this DbTwoContext db2, int id)
        {
            var user = db2.Users.Find(id);

            return new UserModel
            {
                id = user.Id,
                name = user.Name
            };
        }

        public static List<UserModel> GetAttendees(this DbTwoContext db2, int id)
        {
            var model = db2.UserLectures.Where(x => x.LectureId == id).Select(x => new UserModel
            {
                id = x.UserId,
                name = x.User.Name
            }).OrderBy(x => x.name).ToList();

            return model;
        }
    }
}

efcore-multi-db/MultiDb.App/Program.cs

using MultiDb.One;
using MultiDb.Two;
using System;

using (var db1 = new DbOneContext())
{
    using (var db2 = new DbTwoContext())
    {
        DbInitializer.Initialize(db1, db2);

        var lectures = db1.GetLectures(db2);

        foreach (var l in lectures)
        {
            Console.WriteLine(l.name);
        }
        Console.ReadLine();
    }
}