我有一个ASP。NET核心网站。我想使用EF Core查询来获取数据,即使它为null,它也会返回值。我想获得包括OrderNumber
和ItemName
的数据,事件OrderID
为空。我尝试过以下查询,但它只显示了2条具有OrderID
的记录。
请注意:
查询:
var FilteredTransactionData = (from tr in _Context.Transactions
join or in _Context.Orders on tr.OrdersID equals or.OrdersID
join it in _Context.Items on tr.ItemID equals it.ItemID
select new
{
TransactionID = tr.TransactionID,
ItemName = it.ItemName,
OrderNumber = or.OrderNumber,
TransactionQuantity = tr.TransactionQuantity,
}).ToList();
型号:
public class Transactions
{
public long TransactionID { get; set; }
public long ItemID { get; set; } //Relationship
public long? OrderID { get; set; } //Relationship
public decimal TransactionQuantity { get; set; }
}
public class Orders
{
public long OrdersID { get; set; }
public string OrderNumber { get; set; }
}
public class Items
{
public long ItemID { get; set; }
public string ItemName { get; set; }
}
事务表
TransactionID | ItemID | OrderID | TransactionQuantity
----------------+-----------+---------------+-----------------------
1 101 Null 10
2 102 Null 12
3 103 201 20
4 101 202 20
5 102 Null 10
要求输出
TransactionID | ItemName | OrderNumber | TransactionQuantity
----------------+---------------+---------------+-----------------------
1 Item101 10
2 Item102 12
3 Item103 Order201 20
4 Item101 Order202 20
5 Item102 10
join or in _Context.Orders on tr.OrdersID equals or.OrdersID
这里你用OrdersID
连接两张桌子。这就是为什么Null OrdersID行被忽略的原因。这就是加入的实际行为。
您需要做的是将Transactions
表连接到Orders
表。在这种情况下,即使OrdersID
为Null,也会拾取Transactions
表中的所有行。
你可以这样写查询:
var FilteredTransactionData = from tr in _Context.Transactions
join or in _Context.Orders on on tr.OrdersID equals or.OrdersID into gj
from x in gj.DefaultIfEmpty()
select new
{
TransactionID = tr.TransactionID,
ItemName = it.ItemName,
OrderNumber = or.OrderNumber,
TransactionQuantity = tr.TransactionQuantity,
}).ToList();
注意上面的选择into gj
,然后用gj.DefaultIfEmpty()
选择空行