首页 新闻 搜索 专区 学院

求EF CodeFirst的连表查询Lambda表达式,谢谢了

0
悬赏园豆:100 [已解决问题] 解决于 2013-05-16 18:37
//教师  
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)

 

 

PingMac的主页 PingMac | 初学一级 | 园豆:32
提问于:2013-05-14 05:28
< >
分享
最佳答案
0

你的表关系是怎样的呢

收获园豆:50
Yu | 专家六级 |园豆:12944 | 2013-05-14 08:48

已经重新改写问题,并列出关系,谢谢!

PingMac | 园豆:32 (初学一级) | 2013-05-14 12:27

不知道你要找什么,下面两个查询是等同的,提供参考:

 

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 | 园豆:12944 (专家六级) | 2013-05-14 16:41

@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 | 园豆:32 (初学一级) | 2013-05-14 18:25

@PingMac:   

你可以按我提供的写,可以得到你要的"inner join" SQL

Yu | 园豆:12944 (专家六级) | 2013-05-14 20:16
其他回答(2)
0

var list2 = list.Where(m => m.B.CS.Where(o => o.ID == CID ).FirstOrDefault() != null)

收获园豆:50
gunsmoke | 园豆:3592 (老鸟四级) | 2013-05-14 06:31

这个跟我的一样呢,我上面那个CS拼错而已,我想生成的是join的sql语句 现在是 SELECT ID,(SELECT ID FORM ..)这样的  而且这样写不能实现延迟执行;

支持(0) 反对(0) PingMac | 园豆:32 (初学一级) | 2013-05-14 08:00

@PingMac: 

var query = from a in list

      from c in a.B.CS
      where c.ID == CID
      select a;

支持(0) 反对(0) gunsmoke | 园豆:3592 (老鸟四级) | 2013-05-14 08:07

@gunsmoke: 这个可是可行,但是会生成很多句sql,我换成我现在的程序吧,还有关系

支持(0) 反对(0) PingMac | 园豆:32 (初学一级) | 2013-05-14 12:10

@gunsmoke: 

gunsmoke是对的,本质上就是SelectManay

支持(0) 反对(0) alby | 园豆:323 (菜鸟二级) | 2013-05-14 19:55
0

关注中

jerry-Tom | 园豆:4077 (老鸟四级) | 2013-05-15 10:35
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册