//教师 public class Teacher { public string ID { get; set; } public string TrueName { get; set; } public virtual ICollection<Classe> Classes { get; set; } } //班级 public class Classe { public string ID { get; set; } public string ClassName { get; set; } public virtual ICollection<Teacher> Teachers { get; set; } public virtual ICollection<Student> Students { get; set; } } //学生 public class Student { public int ClasseID { get; set; } public string TrueName { get; set; } public virtual Classe Classe { get; set; } } //EF CodeFirst 创建实体 public class Db : DbContext { public Db() { Database.SetInitializer<Db>(null); } public DbSet<Teacher> Teachers { get; set; } public DbSet<Classe> Classes { get; set; } public DbSet<Student> Students { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Classe>().HasMany(r => r.Teachers).WithMany(o => o.Classes).Map(f => { f.MapLeftKey("ClasseID"); f.MapRightKey("TeacherID"); }); base.OnModelCreating(modelBuilder); } } 数据库表 [Students] [Classes] [Teachers] [ClasseTeachers] //用于关联教师和班级的表
中文关系就是:学生属于某个班级,班级里面有多个老师;
现在要在学生里面塞选出教师ID=XX 的学生;
当前写法是:
list = this.StudentService.Where(m => m.IsDeleted == query.IsDeleted && m.Classe.Teachers.Where(o => o.ID == this.UID).Count() > 0);
感觉不科学
我想生成join形式的sql语句
比如:
SELECT dbo.Students.ID, dbo.Students.TrueName
FROM dbo.Students INNER JOIN
dbo.Classes ON dbo.Students.ClasseID = dbo.Classes.ID INNER JOIN
dbo.Teachers ON dbo.Students.ID = dbo.Teachers.ID
WHERE (dbo.Teachers.ID = 15)
你的表关系是怎样的呢
已经重新改写问题,并列出关系,谢谢!
不知道你要找什么,下面两个查询是等同的,提供参考:
var lst = from c in db.Blogs join p in db.Posts on c.Id equals p.BlogId where p.Id==1 select c;
var lst1 = db.Blogs.Join(db.Posts.Where(p=>p.Id==1), blog => blog.Id, post => post.BlogId, (b, p) => new {b});
public class Blog { public int Id { get; set; } public string Title { get; set; } public virtual ICollection<Post> Posts { get; set; } } public class Post { public int Id { get; set; } public string Title { get; set; } public int BlogId { get; set; } }
@Yu: 算了有点难;
exec sp_executesql N'SELECT
[Project1].[ID] AS [ID],
[Project1].[ClasseID] AS [ClasseID],
[Project1].[TrueName] AS [TrueName],
[Project1].[StudentNO] AS [StudentNO],
[Project1].[PassWord] AS [PassWord],
[Project1].[Gender] AS [Gender],
[Project1].[Age] AS [Age],
[Project1].[Email] AS [Email],
[Project1].[HeadPortrait] AS [HeadPortrait],
[Project1].[LoginDate] AS [LoginDate],
[Project1].[LoginIP] AS [LoginIP],
[Project1].[Status] AS [Status],
[Project1].[IsDeleted] AS [IsDeleted],
[Project1].[CreateDate] AS [CreateDate]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Extent1].[ClasseID] AS [ClasseID],
[Extent1].[TrueName] AS [TrueName],
[Extent1].[StudentNO] AS [StudentNO],
[Extent1].[PassWord] AS [PassWord],
[Extent1].[Gender] AS [Gender],
[Extent1].[Age] AS [Age],
[Extent1].[Email] AS [Email],
[Extent1].[HeadPortrait] AS [HeadPortrait],
[Extent1].[LoginDate] AS [LoginDate],
[Extent1].[LoginIP] AS [LoginIP],
[Extent1].[Status] AS [Status],
[Extent1].[IsDeleted] AS [IsDeleted],
[Extent1].[CreateDate] AS [CreateDate],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[ClasseTeachers] AS [Extent2]
WHERE ([Extent1].[ClasseID] = [Extent2].[ClasseID]) AND ([Extent2].[TeacherID] = @p__linq__1)) AS [C1]
FROM [dbo].[Students] AS [Extent1]
) AS [Project1]
WHERE ([Project1].[IsDeleted] = @p__linq__0) AND ([Project1].[C1] > 0) AND (0 = [Project1].[IsDeleted])',N'@p__linq__1 int,@p__linq__0 bit',@p__linq__1=3,@p__linq__0=0
不知道这个查询效率如何:
@PingMac:
你可以按我提供的写,可以得到你要的"inner join" SQL
var list2 = list.Where(m => m.B.CS.Where(o => o.ID == CID ).FirstOrDefault() != null)
这个跟我的一样呢,我上面那个CS拼错而已,我想生成的是join的sql语句 现在是 SELECT ID,(SELECT ID FORM ..)这样的 而且这样写不能实现延迟执行;
@PingMac:
var query = from a in list
from c in a.B.CS
where c.ID == CID
select a;
@gunsmoke: 这个可是可行,但是会生成很多句sql,我换成我现在的程序吧,还有关系
@gunsmoke:
gunsmoke是对的,本质上就是SelectManay
关注中