编写linq similiar，在其中使用SQL查询中的内部联接从中进行选择

debugcn 发表于 Dev

阿里斯很好

如何写SELECT在linq与条款WHERE spesific_field IN从SELECT

模式表

|     absent    |        |      lab      |          |      time     |
|———————————————|        |———————————————|          |———————————————|
|PK| absentID   |   ┌————|PK| labID      |     ┌————|PK| timeID     |
|  | date       |   |    |  | class_room |     |    |  | start      |
|FK| labID      |———┘    |  | timeID     |—————┘    |  | finish     |

如果它是用类似的查询代码编写的

SELECT  COUNT(*)
FROM    absent
WHERE   labID IN ( SELECT  lab.labID
                   FROM    lab INNER JOIN time
                           ON lab.timeID = time.timeID
                   WHERE   lab.class_room = @class_room AND
                           time.start => getdate() and getdate() <= time.finish

[编辑] [班级模型]

[DataContract]
    public class absent
    {
        [DataMember]
        public int absentID { get; set; }

        [DataMember]
        public string name{ get; set; }

        [DataMember]
        public lab labID { get; set; }
    }
}

[DataContract]
    public class lab
    {
        [DataMember]
        public int LabID{ get; set; }

        [DataMember]
        public string class_room { get; set; }

        [DataMember]
        public time timeID { get; set; }
    }
}

[DataContract]
    public class time
    {
        [DataMember]
        public int timeID { get; set; }

        [DataMember]
        public DateTime start { get; set; }

        [DataMember]
        public DateTime finish{ get; set; }
    }
}

GDS

假设您已经从数据库中检索了一个完整的模型，其中包含所有引用的对象，看起来像这样：

        string someClassRoom = null;                //put classroom here
        DateTime someDateTime = default(DateTime);  //put date here
        List<absent> absentCollection = null;       //your collection or db table here
        int count = absentCollection
            .Where(abs => 
                abs.labID.class_room == someClassRoom && 
                abs.labID.timeID.start >= someDateTime && 
                someDateTime <= abs.labID.timeID.finish)
            .Count();

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。