【笔记】EFCore & Mysql

最新推荐文章于 2023-12-18 14:49:57 发布

xueyan0096

最新推荐文章于 2023-12-18 14:49:57 发布

阅读量577

点赞数

文章标签： mysql c#

本文链接：https://blog.csdn.net/xueyan0096/article/details/108285145

版权

1 官网下载mysql免安装版，修改my.ini为

[mysql]
default-character-set = UTF8MB4

[mysqld]
basedir ="C:\Users\huang\Downloads\mysql-8.0.20-winx64"
datadir ="C:\Users\huang\Downloads\mysql-8.0.20-winx64\data"

port=3306

max_connections = 20

character-set-server = UTF8MB4

default-storage-engine = INNODB

default_authentication_plugin = mysql_native_password

[client]
default-character-set = UTF8MB4

进入mysql后将密码修改为root，basedir/datadir应修改为自己的实际路径。再确认C:\Windows\System32\drivers\etc\host文件中有127.0.0.1 localhost这一行。

2 测试用双色球数据

2020050	04	09	17	20	32	33	15	2020-06-14	1101983169	379409292	9664099	5	323895	90	3000	1305	200	61232	10	1226803	5	8175729
2020051	03	06	08	11	19	28	08	2020-06-16	1014976572	342968302	5442583	23	47478	268	3000	3689	200	146306	10	2075975	5	11213884
2020052	02	08	13	29	32	33	15	2020-06-18	1085336824	352815504	10000000	2	568303	53	3000	707	200	49881	10	1105323	5	5849766
2020053	02	14	15	16	32	33	01	2020-06-21	1057074616	381666534	6277278	19	84264	360	3000	1404	200	58928	10	1018510	5	7898492
2020054	03	10	19	25	26	31	02	2020-06-23	1050374135	338817016	6596678	11	104544	210	3000	1818	200	80778	10	1444247	5	8430192
2020055	01	05	07	23	28	30	12	2020-06-25	1009674637	327140694	5761052	14	67264	198	3000	2860	200	124578	10	1889224	5	10927492
2020056	02	05	08	12	26	31	14	2020-06-28	984522456	380233782	6280167	17	146255	186	3000	1223	200	72751	10	1416500	5	9023227
2020057	09	14	21	23	26	32	03	2020-06-30	1019998705	349147892	10000000	3	193145	113	3000	1334	200	68019	10	1289604	5	10655792
2020058	01	03	11	12	19	26	07	2020-07-02	958212978	356052050	5569985	18	94299	136	3000	2821	200	107734	10	1708942	5	15213521
2020059	02	04	10	17	22	25	14	2020-07-05	1002178704	386807160	10000000	4	147308	190	3000	1485	200	88573	10	1671444	5	7739433
2020060	05	09	14	20	24	30	08	2020-07-07	837071557	356937202	5238209	38	20424	554	3000	4357	200	164149	10	2321290	5	12105159
2020061	08	17	24	26	27	31	04	2020-07-09	903858043	366447904	10000000	1	281269	91	3000	1118	200	66022	10	1327960	5	9467898
2020062	10	14	17	22	26	27	05	2020-07-12	932509919	391827962	8901954	5	259438	94	3000	1220	200	63319	10	1218439	5	13187729
2020063	12	15	16	22	29	32	14	2020-07-14	991892696	357076054	10000000	3	289264	103	3000	769	200	50919	10	1099317	5	6461252
2020064	01	03	07	21	27	32	01	2020-07-16	1063056063	356057386	10000000	1	351356	77	3000	919	200	56606	10	1140698	5	8153023
2020065	09	15	18	21	23	26	08	2020-07-19	1090832907	381062334	8838315	5	269544	89	3000	1454	200	70971	10	1281044	5	11879201
2020066	02	09	13	17	26	28	07	2020-07-21	1057341835	352149702	6006277	15	137720	137	3000	2116	200	85857	10	1486743	5	11739149
2020067	04	07	09	23	27	30	08	2020-07-23	1061790227	353153516	6979941	10	176780	140	3000	1518	200	69948	10	1299104	5	8502701
2020068	12	16	21	26	27	32	10	2020-07-26	996910195	376630286	5430373	17	71448	128	3000	3605	200	117265	10	1780262	5	19179292
2020069	03	09	10	13	18	26	04	2020-07-28	1016346427	343151528	7827856	7	249936	99	3000	1278	200	67529	10	1276366	5	7813164
2020070	01	02	04	06	19	21	15	2020-07-30	953784945	343442594	5829067	23	238356	100	3000	951	200	55811	10	1144050	5	9497682
2020071	09	11	12	13	22	23	08	2020-08-02	931954524	363190440	6156653	12	130449	133	3000	2094	200	93662	10	1588088	5	13533760
2020072	06	08	10	15	17	26	04	2020-08-04	945569865	335806408	7525472	7	212479	104	3000	1837	200	80731	10	1505907	5	7887468
2020073	05	07	11	13	27	29	03	2020-08-06	949682723	344984126	7005129	8	155436	129	3000	1913	200	95278	10	1702038	5	9404409
2020074	04	08	09	13	19	33	12	2020-08-09	974585503	375478220	8629293	5	211991	107	3000	1690	200	83978	10	1572292	5	11132696
2020075	03	11	13	20	24	30	16	2020-08-11	1031374526	342593198	10000000	1	155685	143	3000	1703	200	72291	10	1374269	5	9101749
2020076	10	15	16	18	20	27	06	2020-08-13	1033084399	346015916	6895903	8	231207	82	3000	1378	200	83353	10	1385076	5	11811261
2020077	03	10	16	21	25	27	12	2020-08-16	960537693	376430570	5841121	27	87079	326	3000	1359	200	64947	10	1148379	5	8469869
2020078	03	11	14	16	21	32	04	2020-08-18	1029746619	339766446	10000000	1	347407	76	3000	885	200	45994	10	1001391	5	7801189
2020079	05	12	20	21	22	29	14	2020-08-20	1058784536	347031342	8578055	6	146641	183	3000	1179	200	57916	10	1177078	5	7162542
2020080	14	15	18	22	31	33	01	2020-08-23	1122276717	375199530	10000000	2	397581	70	3000	1011	200	54230	10	1096781	5	9535610

3 vs2019新建.net core console项目，通过NuGet添加

Microsoft.EntityFrameworkCore           //ef core
Microsoft.EntityFrameworkCore.Design    //用来在nuget console
Microsoft.EntityFrameworkCore.Tools     //中管理数据迁移
Pomelo.EntityFrameworkCore.MySql        //mysql
Microsoft.Extensions.Logging.Debug      //查看efcore生成的sql

4 准备table class

[Table(name:"Balls")]
public class LottoBalls
{
    /// <summary>
    /// 期号
    /// </summary>
    [Key]
    public int Draw { get; set; }
    /// <summary>
    /// 红球1
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Red1 { get; set; }
    /// <summary>
    /// 红球2
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Red2 { get; set; }
    /// <summary>
    /// 红球3
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Red3 { get; set; }
    /// <summary>
    /// 红球4
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Red4 { get; set; }
    /// <summary>
    /// 红球5
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Red5 { get; set; }
    /// <summary>
    /// 红球6
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Red6 { get; set; }
    /// <summary>
    /// 蓝球
    /// </summary>
    [Column(TypeName = "TINYINT(2) ZEROFILL")]
    public int Blue { get; set; }
    /// <summary>
    /// 开奖日期
    /// </summary>
    [Column(TypeName = "DATE")]
    public DateTime DrawDate { get; set; }


    public LottoPrize Prize { get; set; }
}

[Table(name: "Prize")]
public class LottoPrize
{
    /// <summary>
    /// 期号
    /// </summary>
    [Key]
    public int Draw { get; set; }
    /// <summary>
    /// 奖池金额
    /// </summary>
    [Column(TypeName = "INT UNSIGNED")]
    public int PoolAmount { get; set; }
    /// <summary>
    /// 销售额
    /// </summary>
    [Column(TypeName = "INT UNSIGNED")]
    public int SaleAmount { get; set; }
    /// <summary>
    /// 一等奖金额
    /// </summary>
    [Column(TypeName = "INT UNSIGNED")]
    public int Prize1 { get; set; }
    /// <summary>
    /// 一等奖注数
    /// </summary>
    [Column(TypeName = "SMALLINT UNSIGNED")]
    public int Prize1Bet { get; set; }
    /// <summary>
    /// 二等奖金额
    /// </summary>
    [Column(TypeName = "MEDIUMINT UNSIGNED")]
    public int Prize2 { get; set; }
    /// <summary>
    /// 二等奖注数
    /// </summary>
    [Column(TypeName = "SMALLINT UNSIGNED")]
    public int Prize2Bet { get; set; }
    /// <summary>
    /// 三等奖金额
    /// </summary>
    [Column(TypeName = "SMALLINT UNSIGNED")]
    public int Prize3 { get; set; }
    /// <summary>
    /// 三等奖注数
    /// </summary>
    [Column(TypeName = "MEDIUMINT UNSIGNED")]
    public int Prize3Bet { get; set; }
    /// <summary>
    /// 四等奖金额
    /// </summary>
    [Column(TypeName = "TINYINT UNSIGNED")]
    public int Prize4 { get; set; }
    /// <summary>
    /// 四等奖注数
    /// </summary>
    [Column(TypeName = "MEDIUMINT UNSIGNED")]
    public int Prize4Bet { get; set; }
    /// <summary>
    /// 五等奖金额
    /// </summary>
    [Column(TypeName = "TINYINT UNSIGNED")]
    public int Prize5 { get; set; }
    /// <summary>
    /// 五等奖注数
    /// </summary>
    [Column(TypeName = "MEDIUMINT UNSIGNED")]
    public int Prize5Bet { get; set; }
    /// <summary>
    /// 六等奖金额
    /// </summary>
    [Column(TypeName = "TINYINT UNSIGNED")]
    public int Prize6 { get; set; }
    /// <summary>
    /// 六等奖注数
    /// </summary>
    [Column(TypeName = "INT UNSIGNED")]
    public int Prize6Bet { get; set; }
    /// <summary>
    /// 开奖日期
    /// </summary>
    [Column(TypeName = "DATE")]
    public DateTime DrawDate { get; set; }


    public LottoBalls Balls { get; set; }
}

将开奖号码和中奖信息分为两个表，两个表共享主键实现one-to-one。

5 创建自己的DbContext

public class LottoContext : DbContext
{
    public DbSet<LottoBalls> Balls { get; set; }
    public DbSet<LottoPrize> Prize { get; set; }

    //output to debug
    public static readonly LoggerFactory LoggerFactory =
            new LoggerFactory(new[] { new DebugLoggerProvider() });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseLoggerFactory(LoggerFactory);

        optionsBuilder.UseMySql("server=localhost;userid=root;pwd=root;port=3306;database=unionlotto");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //one-to-one
        modelBuilder.Entity<LottoBalls>()
            .HasOne(b => b.Prize)
            .WithOne(p => p.Balls)
            .HasForeignKey<LottoPrize>(p => p.Draw);

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Red1)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Red2)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Red3)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Red4)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Red5)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Red6)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.Blue)
            .IsRequired();

        modelBuilder.Entity<LottoBalls>()
            .Property(b => b.DrawDate)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.PoolAmount)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.SaleAmount)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize1)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize1Bet)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize2)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize2Bet)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize3)
            .HasDefaultValue(3000);

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize3Bet)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize4)
            .HasDefaultValue(200);

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize4Bet)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize5)
            .HasDefaultValue(10);

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize5Bet)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize6)
            .HasDefaultValue(5);

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.Prize6Bet)
            .IsRequired();

        modelBuilder.Entity<LottoPrize>()
            .Property(p => p.DrawDate)
            .IsRequired();
    }
}

6 打开NuGet console，输入

Add-Migratiion Init
Update-Database

在cmd console中进入Mysql查看数据库(unionlotto)以及表(balls、prize)是否已创建。

7 添加数据

public static void InitialDatabase()
{
    var str = @"2020050	04	09	17	20	32	33	15	2020-06-14	1101983169	379409292	                9664099	5	323895	90	3000	1305	200	61232	10	1226803	5	8175729
2020051	03	06	08	11	19	28	08	2020-06-16	1014976572	342968302	5442583	23	47478	268	3000	3689	200	146306	10	2075975	5	11213884
2020052	02	08	13	29	32	33	15	2020-06-18	1085336824	352815504	10000000	2	568303	53	3000	707	200	49881	10	1105323	5	5849766
2020053	02	14	15	16	32	33	01	2020-06-21	1057074616	381666534	6277278	19	84264	360	3000	1404	200	58928	10	1018510	5	7898492
2020054	03	10	19	25	26	31	02	2020-06-23	1050374135	338817016	6596678	11	104544	210	3000	1818	200	80778	10	1444247	5	8430192
2020055	01	05	07	23	28	30	12	2020-06-25	1009674637	327140694	5761052	14	67264	198	3000	2860	200	124578	10	1889224	5	10927492
2020056	02	05	08	12	26	31	14	2020-06-28	984522456	380233782	6280167	17	146255	186	3000	1223	200	72751	10	1416500	5	9023227
2020057	09	14	21	23	26	32	03	2020-06-30	1019998705	349147892	10000000	3	193145	113	3000	1334	200	68019	10	1289604	5	10655792
2020058	01	03	11	12	19	26	07	2020-07-02	958212978	356052050	5569985	18	94299	136	3000	2821	200	107734	10	1708942	5	15213521
2020059	02	04	10	17	22	25	14	2020-07-05	1002178704	386807160	10000000	4	147308	190	3000	1485	200	88573	10	1671444	5	7739433
2020060	05	09	14	20	24	30	08	2020-07-07	837071557	356937202	5238209	38	20424	554	3000	4357	200	164149	10	2321290	5	12105159
2020061	08	17	24	26	27	31	04	2020-07-09	903858043	366447904	10000000	1	281269	91	3000	1118	200	66022	10	1327960	5	9467898
2020062	10	14	17	22	26	27	05	2020-07-12	932509919	391827962	8901954	5	259438	94	3000	1220	200	63319	10	1218439	5	13187729
2020063	12	15	16	22	29	32	14	2020-07-14	991892696	357076054	10000000	3	289264	103	3000	769	200	50919	10	1099317	5	6461252
2020064	01	03	07	21	27	32	01	2020-07-16	1063056063	356057386	10000000	1	351356	77	3000	919	200	56606	10	1140698	5	8153023
2020065	09	15	18	21	23	26	08	2020-07-19	1090832907	381062334	8838315	5	269544	89	3000	1454	200	70971	10	1281044	5	11879201
2020066	02	09	13	17	26	28	07	2020-07-21	1057341835	352149702	6006277	15	137720	137	3000	2116	200	85857	10	1486743	5	11739149
2020067	04	07	09	23	27	30	08	2020-07-23	1061790227	353153516	6979941	10	176780	140	3000	1518	200	69948	10	1299104	5	8502701
2020068	12	16	21	26	27	32	10	2020-07-26	996910195	376630286	5430373	17	71448	128	3000	3605	200	117265	10	1780262	5	19179292
2020069	03	09	10	13	18	26	04	2020-07-28	1016346427	343151528	7827856	7	249936	99	3000	1278	200	67529	10	1276366	5	7813164
2020070	01	02	04	06	19	21	15	2020-07-30	953784945	343442594	5829067	23	238356	100	3000	951	200	55811	10	1144050	5	9497682
2020071	09	11	12	13	22	23	08	2020-08-02	931954524	363190440	6156653	12	130449	133	3000	2094	200	93662	10	1588088	5	13533760
2020072	06	08	10	15	17	26	04	2020-08-04	945569865	335806408	7525472	7	212479	104	3000	1837	200	80731	10	1505907	5	7887468
2020073	05	07	11	13	27	29	03	2020-08-06	949682723	344984126	7005129	8	155436	129	3000	1913	200	95278	10	1702038	5	9404409
2020074	04	08	09	13	19	33	12	2020-08-09	974585503	375478220	8629293	5	211991	107	3000	1690	200	83978	10	1572292	5	11132696
2020075	03	11	13	20	24	30	16	2020-08-11	1031374526	342593198	10000000	1	155685	143	3000	1703	200	72291	10	1374269	5	9101749
2020076	10	15	16	18	20	27	06	2020-08-13	1033084399	346015916	6895903	8	231207	82	3000	1378	200	83353	10	1385076	5	11811261
2020077	03	10	16	21	25	27	12	2020-08-16	960537693	376430570	5841121	27	87079	326	3000	1359	200	64947	10	1148379	5	8469869
2020078	03	11	14	16	21	32	04	2020-08-18	1029746619	339766446	10000000	1	347407	76	3000	885	200	45994	10	1001391	5	7801189
2020079	05	12	20	21	22	29	14	2020-08-20	1058784536	347031342	8578055	6	146641	183	3000	1179	200	57916	10	1177078	5	7162542
2020080	14	15	18	22	31	33	01	2020-08-23	1122276717	375199530	10000000	2	397581	70	3000	1011	200	54230	10	1096781	5	9535610";

    var lottos = str.Split(Environment.NewLine)
                    .Where(str => !string.IsNullOrWhiteSpace(str))
                    .Select(str => str.Split("\t"));
    using(var context = new LottoContext())
    {
        AddDataRange(context, lottos);
    }
}

private static void AddDataRange(LottoContext context, IEnumerable<string[]> lottos)
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            foreach (var lotto in lottos)
            {
                var balls = ExtractBalls(lotto);
                var prize = ExtractPrize(lotto);

                context.Balls.Add(balls);
                context.Prize.Add(prize);
            }
            context.SaveChanges();

            transaction.Commit();
        }
        catch (Exception e)
        {
            Debug.WriteLine(e.Message);
        }
    }
}

public static LottoBalls ExtractBalls(string[] lotto)
    => new LottoBalls
    {
        Draw = int.Parse(lotto[0]),
        Red1 = int.Parse(lotto[1]),
        Red2 = int.Parse(lotto[2]),
        Red3 = int.Parse(lotto[3]),
        Red4 = int.Parse(lotto[4]),
        Red5 = int.Parse(lotto[5]),
        Red6 = int.Parse(lotto[6]),
        Blue = int.Parse(lotto[7]),
        DrawDate = DateTime.Parse(lotto[8])
    };

public static LottoPrize ExtractPrize(string[] lotto)
    => new LottoPrize
    {
        Draw = int.Parse(lotto[0]),
        PoolAmount = int.Parse(lotto[9]),
        SaleAmount = int.Parse(lotto[10]),
        Prize1 = int.Parse(lotto[11]),
        Prize1Bet = int.Parse(lotto[12]),
        Prize2 = int.Parse(lotto[13]),
        Prize2Bet = int.Parse(lotto[14]),
        Prize3 = int.Parse(lotto[15]),
        Prize3Bet = int.Parse(lotto[16]),
        Prize4 = int.Parse(lotto[17]),
        Prize4Bet = int.Parse(lotto[18]),
        Prize5 = int.Parse(lotto[19]),
        Prize5Bet = int.Parse(lotto[20]),
        Prize6 = int.Parse(lotto[21]),
        Prize6Bet = int.Parse(lotto[22]),
        DrawDate = DateTime.Parse(lotto[8])
    };

8 UnitTest

[TestMethod]
public async Task TestMethod1()
{
    using (var context = new LottoContext())
    {
        var query = from lotto in context.Balls
                    select $"{lotto.Draw}\t{lotto.Red1}\t{lotto.Red2}\t" +
                           $"{lotto.Red3}\t{lotto.Red4}\t{lotto.Red5}\t" +
                           $"{lotto.Red6}\t{lotto.DrawDate}";

        Debug.WriteLine(string.Join(Environment.NewLine, query));
    }
}

运行测试就能够看到上面输入的数据了，在调试窗口还能看到efcore翻译的sql语句。

SELECT `b`.`Draw`, `b`.`Blue`, `b`.`DrawDate`, `b`.`Red1`, `b`.`Red2`, `b`.`Red3`, `b`.`Red4`, `b`.`Red5`, `b`.`Red6`
FROM `Balls` AS `b`

EFCore 使用sql语句查询

//无参sql语句
//最后一期
var query = context.Balls.FromSqlRaw("SELECT * FROM balls ORDER BY draw DESC LIMIT 1");
//带参数的sql语句
//期号大于2020070的
var _draw = 2020070;
query = context.Balls.FromSqlInterpolated($"SELECT * FROM balls WHERE draw > {_draw}");

EFCore 上述几种查询的限制是只能返回表的所有列数据。

如果要统计红球三分区数据时就得借助sql udf。EFCore 只能映射scalar udf，就是那种只返回一个值的db function。根据官网文档，udf 的EFCore映射方法声明在DbContext类中，方法名字与udf一致且默认不区分大小写，返回值类型和参数类型要和udf一致，参数名可以随意。sql udf如下，通过context.Database.ExecuteSqlRaw方法添加到数据库

//udf开关，设为on才能添加udf
context.Database.ExecuteSqlRaw(@"SET global log_bin_trust_function_creators = on");
//红球三分区
context.Database.ExecuteSqlRaw(@"DROP FUNCTION IF EXISTS redp3count;
    CREATE FUNCTION redp3count(red1 INT, red2 INT, red3 INT, red4 INT, red5 INT, red6 INT, flag INT)
    RETURNS INT
    BEGIN
	    DECLARE Count INT DEFAULT 0;

	    IF (red1 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
	    IF (red2 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
	    IF (red3 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
	    IF (red4 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
	    IF (red5 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
	    IF (red6 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
	
	    RETURN Count;
    END");

在LottoContext中添加映射方法，方法带DbFunction注释

public class LottoContext : DbContext
{
    ......

    /// <summary>
    /// 红球三分区个数
    /// </summary>
    /// <param name="index">0: 第一区，1: 第二区，2: 第三区</param>
    [DbFunction]
    public static int RedP3Count(int red1, int red2, int red3, int red4, int red5, int         red6, int index)
        => throw new NotImplementedException();

    ......
}

如果想知道红球三区个数

var query = from lotto in context.Balls
            select new
            {
                draw = lotto.Draw,
                p1 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
                                lotto.Red5, lotto.Red6, 0),
                p2 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
                                lotto.Red5, lotto.Red6, 1),
                p3 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
                                lotto.Red5, lotto.Red6, 2)
            } into t
            select $"{t.draw}\t{t.p1}:{t.p2}:{t.p3}";

Debug.WriteLine(string.Join(Environment.NewLine, query));


结果如下：
2020050	2:2:2
2020051	4:1:1
2020052	2:1:3
2020053	1:3:2
2020054	2:1:3
2020055	3:0:3
2020056	3:1:2
2020057	1:2:3
2020058	3:2:1
2020059	3:2:1
2020060	2:2:2
2020061	1:1:4
2020062	1:3:2
2020063	0:4:2
2020064	3:1:2
2020065	1:3:2
2020066	2:2:2
2020067	3:0:3
2020068	0:3:3
2020069	3:2:1
2020070	4:2:0
2020071	2:3:1
2020072	3:2:1
2020073	3:1:2
2020074	3:2:1
2020075	2:2:2
2020076	1:4:1
2020077	2:2:2
2020078	2:3:1
2020079	1:4:1
2020080	0:4:2

如果想知道哪些三区个数现的次数多些

var query = from lotto in context.Balls
            select new
            {
                p1 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
                                    lotto.Red5, lotto.Red6, 0),
                p2 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
                                    lotto.Red5, lotto.Red6, 1),
                p3 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
                                    lotto.Red5, lotto.Red6, 2)
            } into t1
            group t1 by new { t1.p1, t1.p2, t1.p3} into g
            select new { Key = g.Key, Count = g.Count() } into t2
            orderby t2.Count descending
            select $"{t2.Key.p1}:{t2.Key.p2}:{t2.Key.p3}\t{t2.Count}";

Debug.WriteLine(string.Join(Environment.NewLine, query));


结果如下：
2:2:2	5
3:2:1	5
1:3:2	3
3:1:2	3
2:1:3	2
3:0:3	2
0:4:2	2
2:3:1	2
1:4:1	2
4:1:1	1
1:2:3	1
1:1:4	1
0:3:3	1
4:2:0	1

如果想统计红球频率

var query = from red in (from lotto in context.Balls
                         select lotto.Red1)
                 .Concat(from lotto in context.Balls
                         select lotto.Red2)
                 .Concat(from lotto in context.Balls
                         select lotto.Red3)
                 .Concat(from lotto in context.Balls
                         select lotto.Red4)
                 .Concat(from lotto in context.Balls
                         select lotto.Red5)
                 .Concat(from lotto in context.Balls
                         select lotto.Red6)
            group red by red into g
            select new { red = g.Key, count = g.Count() } into t
            orderby t.red
            select $"{t.red}\t{t.count}";

Debug.WriteLine(string.Join(Environment.NewLine, query));


结果如下：
1	4
2	6
3	8
4	5
5	5
6	3
7	4
8	6
9	9
10	7
11	6
12	6
13	7
14	6
15	6
16	6
17	6
18	4
19	5
20	5
21	8
22	6
23	5
24	3
25	3
26	11
27	8
28	3
29	4
30	4
31	4
32	8
33	5

如果想通过sql view来查询红球频率，首先通过sql语句添加view

//红球频率
context.Database.ExecuteSqlRaw(@"DROP VIEW IF EXISTS RedFreq;
		CREATE VIEW RedFreq AS
		SELECT t.red, COUNT(*) AS count
		FROM (SELECT red1 AS red FROM balls UNION ALL
				SELECT red2 AS red FROM balls UNION ALL
				SELECT red3 AS red FROM balls UNION ALL
				SELECT red4 AS red FROM balls UNION ALL
				SELECT red5 AS red FROM balls UNION ALL
				SELECT red6 AS red FROM balls
				) AS t
		GROUP BY t.red
		ORDER BY t.red");

接着新建类RedFreq并在LottoContext里添加映射

public class RedFreq
{
    public int Red { get; set; }
    public int Count { get; set; }
}

public class LottoContext : DbContext
{
    ......
    public DbSet<RedFreq> RedFreq { get; set; }
    ......
}

接着在LottoContext的OnModelCreating方法里添加

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    ......

    modelBuilder.Entity<RedFreq>(rf => {
        rf.ToView("RedFreq");
        rf.HasNoKey();
        rf.Property(e => e.Red).HasColumnName("red");
        rf.Property(e => e.Count).HasColumnName("count");
    });

    ......
}

这样通过如下代码可以得到相同结果

var query = from freq in context.RedFreq
            select $"{freq.Red}\t{freq.Count}";
Debug.WriteLine(string.Join(Environment.NewLine, query));

xueyan0096

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【笔记】EFCore & Mysql

1 官网下载mysql免安装版，修改my.ini为[mysql]default-character-set = UTF8MB4[mysqld]basedir ="C:\Users\huang\Downloads\mysql-8.0.20-winx64"datadir ="C:\Users\huang\Downloads\mysql-8.0.20-winx64\data"port=3306max_connections = 20character-set-server = UTF.
复制链接

扫一扫