c# EF与LINQ
由于用的是自动生成的，这个首先是在ef安装完成的情况下进行
然后选中自己的项目
这样就会出来一个文件夹
新建一个配置项
内容为：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="proxyMySQL" connectionString="Server=localhost;port=端口（默认是3306）;Database=数据库;Uid=root;Pwd=密码;" providerName="" />
</connectionStrings>
</configuration>
然后是
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Xml.Linq;
using EF_LINQ.Models;
namespace EF_LINQ
{
class EFModels
{
learndbContext db=new learndbContext();
//开始处理
/*实现一个菜单，菜单项包括:（期末成绩为score1)
O、显示课程信息:每条信息占一行，信息项包括课程号、课程名、学分，各项以Tab分隔。
1、按班名查询成绩:每条成绩信息占一行，信息项包括学号、姓名、课程名、期末成绩，各项以Tab分隔。
2、按姓名查询成绩:每条成绩信息占一行，信息项包括学号、姓名、专业班级、课程名、期末成绩，各项以Tab分隔。
3、插入成绩:输入学号、课程号、学期与期末成绩并保存
4、修改信息:输入学号与课程号，修改其期末成绩为指定值。
5、删除信息:输入学号、课程号，删除成绩记录
6、退出系统*/
public void displayUcourse()
{
//显示信息
var results = db.Ucourse;//已经是一个集合了
if (results != null)
{
foreach (var r in results)
{
Console.WriteLine($"{r.Cid}\t{r.Cname}\t\t{r.Credit}");
}
}
else
Console.WriteLine("not found!");
}
public void queryuscWithgrade(string gid)
{
var results = from ustudent in db.Ustudent
join ugrade in db.Ugrade on ustudent.Gid equals ugrade.Gid
join usc in db.Usc on ustudent.Sid equals usc.Sid
join ucourse in db.Ucourse on usc.Cid equals ucourse.Cid
where ustudent.Gid.Contains(gid)
select new {ustudent.Sid,ustudent.Sname,ucourse.Cname,usc.Score1};
if (results.Any())
{
foreach (var r in results)
{
Console.WriteLine($"{r.Sid}\t{r.Sname}\t{r.Cname}\t{r.Score1}");
}
}
}
public void equeryuscWithname(string name)
{
var results = from ustudent in db.Ustudent
join ugrade in db.Ugrade on ustudent.Gid equals ugrade.Gid
join usc in db.Usc on ustudent.Sid equals usc.Sid
join ucourse in db.Ucourse on usc.Cid equals ucourse.Cid
where ustudent.Sname.Contains(name)
select new { ustudent.Sid, ustudent.Sname, ugrade.Gname, ucourse.Cname, usc.Score1 };
if (results.Any())
{
foreach (var r in results)
{
Console.WriteLine($"{r.Sid}\t{r.Sname}\t{r.Gname}\t{r.Cname}\t{r.Score1}");
}
}
}
public void insertustudent()
{
Ustudent ustudent = new Ustudent();
Console.WriteLine("请输入插入学生的学号");
string b = Console.ReadLine();
ustudent.Sid = b;
Console.WriteLine("请输入插入学生的姓名");
string sname = Console.ReadLine();
ustudent.Sname = sname;
Console.WriteLine("请输入插入学生的性别");
string sex = Console.ReadLine();
ustudent.Ssexy = sex;
Console.WriteLine("请输入插入学生的出生日期 注意格式为1876-10-2");
string date = Console.ReadLine();
ustudent.Sbdate = Convert.ToDateTime(date);
Console.WriteLine("请输入插入学生的专业班级");
string class_studnt = Console.ReadLine();
ustudent.Gid =class_studnt;
ustudent.Qq = "000";
ustudent.Stele = "000";
db.Ustudent.Add(ustudent);
try
{
db.SaveChanges();
Console.WriteLine("新增成功");
}
catch (Exception dbe)
{
Console.WriteLine("错误信息：{0}", dbe.Message);
}
}
public void updateOne()
{
//查询目标记录，然后修改各属性值
Console.WriteLine("请输入学生的学号");
string b = Console.ReadLine();
Console.WriteLine("请输入课程号");
string c = Console.ReadLine();
var r = db.Usc.Where(o =>( o.Sid ==b && o.Cid==c) ).FirstOrDefault();
try
{
Console.WriteLine("请输入修改的期末成绩");
Double d = double.Parse(Console.ReadLine());
r.Score1 = d;
db.SaveChanges();
Console.WriteLine("修改成功");
}
catch (Exception dbe)
{
Console.WriteLine("错误信息：{0}", dbe.Message);
}
}
//删除记录
public void deleteusc()
{
//删除记录
Console.WriteLine("请输入学生的学号");
string b = Console.ReadLine();
Console.WriteLine("请输入课程号");
string c = Console.ReadLine();
var rt = db.Usc.Where(o =>( o.Sid == b&& o.Cid==c)).ToList<Usc>();
db.Usc.RemoveRange(rt);
try
{
db.SaveChanges();
Console.WriteLine("删除成功");
}
catch (Exception dbe)
{
Console.WriteLine("错误信息：{0}", dbe.Message);
}
}
}
}
然后是主函数：
using EF_LINQ;
class test
{
static public void show()
{
Console.WriteLine("---------------------");
Console.WriteLine("请输入你的选择 注意选择6或者其他数字将要退出了");
Console.WriteLine("O、显示课程信息: 每条信息占一行，信息项包括课程号、课程名、学分，各项以Tab分隔。");
Console.WriteLine("1、按班名查询成绩: 每条成绩信息占一行，信息项包括学号、姓名、课程名、期末成绩，各项以Tab分隔。");
Console.WriteLine("2、按姓名查询成绩: 每条成绩信息占一行，信息项包括学号、姓名、专业班级、课程名、期末成绩，各项以Tab分隔。");
Console.WriteLine("3、插入成绩: 输入学号、课程号、学期与期末成绩并保存");
Console.WriteLine("4、修改信息: 输入学号与课程号，修改其期末成绩为指定值。");
Console.WriteLine("5、删除信息: 输入学号、课程号，删除成绩记录");
Console.WriteLine("6、退出系统");
Console.WriteLine("---------------------");
}
static void Main(string[] args)
{
EFModels ef = new EFModels();
int flag = 1;
int a;
while (flag != -1)
{
show();
a = Convert.ToInt32(Console.ReadLine());
switch (a)
{
case 0:
Console.WriteLine("课程号\t课程名\t学分");
ef.displayUcourse();
break;
case 1:
Console.WriteLine("输入班级编号");
string b = Console.ReadLine();
Console.WriteLine("学号\t姓名\t课程\t\t\t期末成绩score1");
ef.queryuscWithgrade(b);
break;
case 2:
Console.WriteLine("输入姓名");
string c = Console.ReadLine();
Console.WriteLine("学号\t姓名\t专业班级\t课程名\t期末成绩");
ef.equeryuscWithname(c);
break;
case 3:
ef.insertustudent();
break;
case 4:
ef.updateOne();
break;
case 5:
ef.deleteusc();
break;
default:
flag = -1;
break;
}
}
}
}
/*Console.ReadKey();*/
对于自动生成的类
因为在删除修改插入等一些惭怍中，可能会出现数据库主键确实的问题，导致无法插入成功
我给出我的成功的类：
// <auto-generated> This file has been auto generated by EF Core Power Tools. </auto-generated>
#nullable disable
using System;
using System.Collections.Generic;
using System.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace EF_LINQ.Models
{
public partial class learndbContext : DbContext
{
string connectionString = "";
public learndbContext()
{
connectionString = ConfigurationManager.ConnectionStrings["proxyMySQL"].ToString();
//Console.WriteLine(connectionString);
//Console.WriteLine("经过这里了");
}
public learndbContext(DbContextOptions<learndbContext> options)
: base(options)
{
}
public virtual DbSet<Employee> Employee { get; set; }
public virtual DbSet<Test> Test { get; set; }
public virtual DbSet<Ucourse> Ucourse { get; set; }
public virtual DbSet<Udept> Udept { get; set; }
public virtual DbSet<Ugrade> Ugrade { get; set; }
public virtual DbSet<Ujobtable> Ujobtable { get; set; }
public virtual DbSet<Usc> Usc { get; set; }
public virtual DbSet<Ustudent> Ustudent { get; set; }
public virtual DbSet<Uteacher> Uteacher { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseMySql(connectionString, Microsoft.EntityFrameworkCore.ServerVersion.Parse("8.0.12-mysql"));
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.UseCollation("utf8_unicode_ci")
.HasCharSet("utf8");
modelBuilder.Entity<Employee>(entity =>
{
entity.HasNoKey();
entity.ToTable("employee");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.EmpId)
.HasMaxLength(4)
.HasColumnName("emp_id");
entity.Property(e => e.Fname)
.HasMaxLength(5)
.HasColumnName("fname");
entity.Property(e => e.HireDate)
.HasColumnType("timestamp")
.ValueGeneratedOnAddOrUpdate()
.HasColumnName("hire_date")
.HasDefaultValueSql("CURRENT_TIMESTAMP");
entity.Property(e => e.JobId)
.HasColumnType("smallint(6)")
.HasColumnName("job_id");
entity.Property(e => e.JobLvl)
.HasColumnType("int(11)")
.HasColumnName("job_lvl");
entity.Property(e => e.Lname)
.HasMaxLength(4)
.HasColumnName("lname");
entity.Property(e => e.PubId)
.HasMaxLength(4)
.HasColumnName("pub_id");
entity.Property(e => e.Sexy)
.HasMaxLength(2)
.HasColumnName("sexy");
});
modelBuilder.Entity<Test>(entity =>
{
entity.HasNoKey();
entity.ToTable("test");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Sid)
.HasMaxLength(12)
.HasColumnName("sid");
entity.Property(e => e.Sname).HasMaxLength(10);
});
modelBuilder.Entity<Ucourse>(entity =>
{
entity.HasKey(e => e.Cid).HasName("PRIMARY");
entity.ToTable("ucourse");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Cattr)
.HasMaxLength(6)
.HasColumnName("cattr")
.IsFixedLength();
entity.Property(e => e.Chour)
.HasColumnType("smallint(6)")
.HasColumnName("chour");
entity.Property(e => e.Cid)
.HasMaxLength(4)
.IsFixedLength();
entity.Property(e => e.Cname)
.HasMaxLength(20)
.IsFixedLength();
entity.Property(e => e.Cnum)
.HasColumnType("int(11)")
.HasColumnName("cnum");
entity.Property(e => e.Credit).HasColumnName("credit");
entity.Property(e => e.Did)
.HasMaxLength(2)
.HasColumnName("did")
.IsFixedLength();
entity.Property(e => e.Pcid)
.HasMaxLength(4)
.HasColumnName("pcid")
.IsFixedLength();
});
modelBuilder.Entity<Udept>(entity =>
{
entity.HasKey(e => e.Did).HasName("PRIMARY");
entity.ToTable("udept");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Daddr)
.HasMaxLength(20)
.HasColumnName("daddr");
entity.Property(e => e.Demail)
.HasMaxLength(50)
.HasColumnName("demail");
entity.Property(e => e.Did)
.HasMaxLength(2)
.HasColumnName("did")
.IsFixedLength();
entity.Property(e => e.Dname)
.HasMaxLength(20)
.HasColumnName("dname");
entity.Property(e => e.Dtele)
.HasMaxLength(16)
.HasColumnName("dtele");
});
modelBuilder.Entity<Ugrade>(entity =>
{
entity.HasKey(e => e.Gid).HasName("PRIMARY");
entity.ToTable("ugrade");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Did)
.HasMaxLength(2)
.HasColumnName("did")
.IsFixedLength();
entity.Property(e => e.Gid)
.HasMaxLength(2)
.HasColumnName("gid")
.IsFixedLength();
entity.Property(e => e.Gname)
.HasMaxLength(20)
.HasColumnName("gname")
.IsFixedLength();
entity.Property(e => e.Gyear)
.HasColumnType("int(11)")
.HasColumnName("gyear");
entity.Property(e => e.Tid)
.HasMaxLength(5)
.HasColumnName("tid")
.IsFixedLength();
});
modelBuilder.Entity<Ujobtable>(entity =>
{
entity.HasKey(e => e.Jid).HasName("PRIMARY");
entity.ToTable("ujobtable");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Cid)
.HasMaxLength(4)
.HasColumnName("cid")
.IsFixedLength();
entity.Property(e => e.Gid)
.HasMaxLength(2)
.HasColumnName("gid")
.IsFixedLength();
entity.Property(e => e.Jid)
.HasColumnType("int(11)")
.HasColumnName("jid");
entity.Property(e => e.Room)
.HasMaxLength(10)
.HasColumnName("room")
.IsFixedLength();
entity.Property(e => e.Term)
.HasMaxLength(12)
.HasColumnName("term")
.IsFixedLength();
entity.Property(e => e.Tid)
.HasMaxLength(5)
.HasColumnName("tid")
.IsFixedLength();
entity.Property(e => e.Timeseg)
.HasMaxLength(8)
.HasColumnName("timeseg")
.IsFixedLength();
entity.Property(e => e.Week)
.HasColumnType("int(11)")
.HasColumnName("week");
});
modelBuilder.Entity<Usc>(entity =>
{
entity.HasKey(e => e.Scid).HasName("PRIMARY");
entity.ToTable("usc");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Cid)
.HasMaxLength(4)
.HasColumnName("cid")
.IsFixedLength();
entity.Property(e => e.Scid)
.HasColumnType("bigint(20)")
.HasColumnName("scid");
entity.Property(e => e.Score).HasColumnName("score");
entity.Property(e => e.Score1).HasColumnName("score1");
entity.Property(e => e.Score2).HasColumnName("score2");
entity.Property(e => e.Score3).HasColumnName("score3");
entity.Property(e => e.Sid)
.HasMaxLength(9)
.HasColumnName("sid")
.IsFixedLength();
entity.Property(e => e.Term)
.HasMaxLength(12)
.HasColumnName("term")
.IsFixedLength();
});
modelBuilder.Entity<Ustudent>(entity =>
{
entity.HasKey(e => e.Sid).HasName("PRIMARY");
entity.ToTable("ustudent");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Gid)
.HasMaxLength(2)
.HasColumnName("gid")
.IsFixedLength();
entity.Property(e => e.Qq)
.HasMaxLength(15)
.HasColumnName("QQ");
entity.Property(e => e.Sbdate)
.HasColumnType("datetime")
.ValueGeneratedOnAddOrUpdate()
.HasDefaultValueSql("CURRENT_TIMESTAMP");
entity.Property(e => e.Sid)
.HasMaxLength(9)
.IsFixedLength();
entity.Property(e => e.Sname)
.HasMaxLength(8)
.IsFixedLength();
entity.Property(e => e.Ssexy)
.HasMaxLength(2)
.IsFixedLength();
entity.Property(e => e.Stele)
.HasMaxLength(11)
.HasColumnName("stele")
.IsFixedLength();
});
modelBuilder.Entity<Uteacher>(entity =>
{
entity.HasKey(e=>e.Tid).HasName("PRIMARY");
entity.ToTable("uteacher");
entity.UseCollation("utf8_general_ci");
entity.Property(e => e.Did)
.HasMaxLength(2)
.HasColumnName("did")
.IsFixedLength();
entity.Property(e => e.Email)
.HasMaxLength(30)
.HasColumnName("email")
.IsFixedLength();
entity.Property(e => e.Msn)
.HasMaxLength(30)
.HasColumnName("msn")
.IsFixedLength();
entity.Property(e => e.Qq)
.HasMaxLength(12)
.HasColumnName("qq")
.IsFixedLength();
entity.Property(e => e.Tbdate)
.HasColumnType("datetime")
.ValueGeneratedOnAddOrUpdate()
.HasColumnName("tbdate")
.HasDefaultValueSql("CURRENT_TIMESTAMP");
entity.Property(e => e.Tele)
.HasMaxLength(16)
.HasColumnName("tele")
.IsFixedLength();
entity.Property(e => e.Tfield)
.HasMaxLength(50)
.HasColumnName("tfield")
.IsFixedLength();
entity.Property(e => e.Tid)
.HasMaxLength(5)
.HasColumnName("tid")
.IsFixedLength();
entity.Property(e => e.Tname)
.HasMaxLength(8)
.HasColumnName("tname")
.IsFixedLength();
entity.Property(e => e.Tprof)
.HasMaxLength(10)
.HasColumnName("tprof")
.IsFixedLength();
entity.Property(e => e.Tsexy)
.HasMaxLength(2)
.HasColumnName("tsexy")
.IsFixedLength();
});
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
}
可以看到其实我只是加了一点的配置比原来的，然后就是增加了主键，因为默认是无主键的，所以haskey(）我就增添了相应表中的主键
以上代码仅供参考，首先这个代码我电脑是可以跑的，然后出现什么问题欢迎交流
