使用分段创建Linq查询和外键的问题 [英] Issue with piece wise created Linq query and foreign keys

问题描述

嗨！

数据库表格：

人[Id（PK），姓名等]，预约[Id（PK），Company，personId （FK），startTime等......]因此人与约会之间存在一对多的关系

c＃c​​ode
var query = from p在数据库中。人员选择p;
如果（公司！= null）
查询=来自查询中的p，来自p.appointments中其中a.company == company
选择p;

如果（startTime！= null）
查询=来自查询中的p，来自p中的q.appointments
其中a.startTime == startTime
选择p;

好的，所以如果上述if语句中只有一个是真的，那就像我打算做的那样。

但是如果两者都为真，则会生成一个查询，其中为约会表和每个表refe创建两个别名在查询中只有一个条件被应用，因此结果在每个约会表别名的结果之间产生一个交叉产品（如果我理解的话）。

但是当然我想要做什么第二个if块（如果两个if都为真）是在约会表上添加另一个条件。也就是说，相当于：
查询=来自查询中的p，来自p.appointments中的q，其中a.startTime == startTime && a.company == company
选择p;

如何在上述逻辑中写这个？

BR

Hi!

The database tables:

Person [Id (PK), Name, etc...], Appointment [Id (PK), Company, personId (FK), startTime, etc...]

So there is a one to many relationship between Person and Appointment

The c# code
var query = from p in database.Persons select p;
if(company != null)
   query = from p in query
               from a in p.appointments
               where a.company == company
               select p;

if(startTime != null)
   query = from p in query
               from q in p.appointments
               where a.startTime == startTime
               select p;

Ok, so if one and only one of the above if statements are true it works like I intend to do.

But if both are true it generates a query where it creates two aliases for the appointments table, and for each table reference in the query only one of the conditions are applied, so the result generates a crossproduct between the results of each appointment table alias (if i understand it right).

But ofcourse what I want to do in the second if block (if both ifs are true) is to just add another condition on the appointment table. That is, the equivalent of:
   query = from p in query
               from q in p.appointments
               where a.startTime == startTime && a.company == company
               select p;

How do I write this in the above logic?

BR

推荐答案

我相信这就是你想要的：

I believe this is what you want:

var query = from p in query
               from a in p.appointments
               select p;



if (startTime != null)
   query = query.Where(p => p.startTime == startTime);

if (company != null)
   query = query.Where(p => p.company == company);

这篇关于使用分段创建Linq查询和外键的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！