dotnet 多数据库 sqlite efcore model和entity区别 一对多 多对一 多对多

efcore-multi-db/MultiDb.sln

Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio 15
VisualStudioVersion = 15.0.27130.2024
MinimumVisualStudioVersion = 10.0.40219.1
Project("{9A19103F-16F7-4668-BE54-9A1E7A4F7556}") = "MultiDb.App", "MultiDb.App\MultiDb.App.csproj", "{94622556-3ECC-44F3-977A-8BC9D1555F5F}"
EndProject
Project("{9A19103F-16F7-4668-BE54-9A1E7A4F7556}") = "MultiDb.One", "MultiDb.One\MultiDb.One.csproj", "{19B80317-1494-41F3-B139-BA4E1CB83D82}"
EndProject
Project("{9A19103F-16F7-4668-BE54-9A1E7A4F7556}") = "MultiDb.Two", "MultiDb.Two\MultiDb.Two.csproj", "{687DEA51-74E0-4628-8979-4FDCE9FFF2BE}"
EndProject
Global
	GlobalSection(SolutionConfigurationPlatforms) = preSolution
		Debug|Any CPU = Debug|Any CPU
		Release|Any CPU = Release|Any CPU
	EndGlobalSection
	GlobalSection(ProjectConfigurationPlatforms) = postSolution
		{94622556-3ECC-44F3-977A-8BC9D1555F5F}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
		{94622556-3ECC-44F3-977A-8BC9D1555F5F}.Debug|Any CPU.Build.0 = Debug|Any CPU
		{94622556-3ECC-44F3-977A-8BC9D1555F5F}.Release|Any CPU.ActiveCfg = Release|Any CPU
		{94622556-3ECC-44F3-977A-8BC9D1555F5F}.Release|Any CPU.Build.0 = Release|Any CPU
		{19B80317-1494-41F3-B139-BA4E1CB83D82}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
		{19B80317-1494-41F3-B139-BA4E1CB83D82}.Debug|Any CPU.Build.0 = Debug|Any CPU
		{19B80317-1494-41F3-B139-BA4E1CB83D82}.Release|Any CPU.ActiveCfg = Release|Any CPU
		{19B80317-1494-41F3-B139-BA4E1CB83D82}.Release|Any CPU.Build.0 = Release|Any CPU
		{687DEA51-74E0-4628-8979-4FDCE9FFF2BE}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
		{687DEA51-74E0-4628-8979-4FDCE9FFF2BE}.Debug|Any CPU.Build.0 = Debug|Any CPU
		{687DEA51-74E0-4628-8979-4FDCE9FFF2BE}.Release|Any CPU.ActiveCfg = Release|Any CPU
		{687DEA51-74E0-4628-8979-4FDCE9FFF2BE}.Release|Any CPU.Build.0 = Release|Any CPU
	EndGlobalSection
	GlobalSection(SolutionProperties) = preSolution
		HideSolutionNode = FALSE
	EndGlobalSection
	GlobalSection(ExtensibilityGlobals) = postSolution
		SolutionGuid = {3774E136-D135-4DE9-AED8-C7A132717A00}
	EndGlobalSection
EndGlobal

efcore-multi-db/global.json

{
  "sdk": {
    "version": "7.0.306"
  }
}

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/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/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

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; }
    }
}

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>

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;

namespace MultiDb.App
{
    class Program
    {
        static void Main(string[] args)
        {
            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.WriteLine($"Proctor: {l.proctor.name}");
                        Console.WriteLine("Attendees:");

                        foreach (var a in l.attendees)
                        {
                            Console.WriteLine(a.name);
                        }
                        Console.WriteLine();
                    }

                    Console.ReadLine();
                }
            }
        }
    }
}