postgresql - PostgreSQL 和 Entity Framework Core 3.1 生成包含内联查询/子查询的查询
问题描述
我在 PostgreSQL 数据库上有一个 .NET Core / EF Core 3.1 应用程序。我正在寻找客户。目前,我正在两次访问数据库。第一次是为我找到与我的搜索词匹配的所有客户 ID,然后第二次查询检索该客户的所有相关实体并一次性将它们带回来。例如:
按名称和邮政编码获取客户 ID:
customerIds = (from c in loyalty.Customer.AsNoTracking()
where c.FirstName.ToLower() == request.CustomerSearchParameters.FirstName.ToLower() &&
c.LastName.ToLower() == request.CustomerSearchParameters.LastName.ToLower()
join a in loyalty.Address on c.CustomerInternalId equals a.CustomerInternalId
where a.Postcode == request.CustomerSearchParameters.PostCode
select c.CustomerInternalId).ToList();
然后获取这些客户 ID 的所有实体。
var customers = (from c in loyalty.Customer.AsNoTracking()
where customerIds.Contains(c.CustomerInternalId)
select c)
.Include(c => c.ContactInformation).AsNoTracking()
.Include(c => c.Address).AsNoTracking()
.Include(c => c.MarketingPreferences).AsNoTracking()
.Include(c => c.ContentTypePreferences).AsNoTracking()
.Include(c => c.ExternalCards).AsNoTracking().ToList();
这显然会产生两个查询:
第一的:
SELECT c.customer_internal_id
FROM customer AS c
INNER JOIN address AS a ON c.customer_internal_id = a.customer_internal_id
WHERE ((LOWER(c.first_name) = @__ToLower_0) AND (LOWER(c.last_name) = @__ToLower_1)) AND (a.postcode = @__request_CustomerSearchParameters_PostCode_2)
然后
SELECT c.customer_internal_id, c.business_partner_id, c.created_date, c.customer_type, c.date_of_birth, c.first_name, c.gender, c.home_store_id, c.home_store_updated, c.last_name, c.loyalty_db_id, c.mca_id, c.status, c.status_reason, c.store_joined, c.title, c.updated_by, c.updated_date, c.updating_store, c0.contact_internal_id, c0.contact_type, c0.contact_value, c0.created_date, c0.customer_internal_id, c0.updated_by, c0.updated_date, c0.updating_store, c0.validated, a.address_internal_id, a.address_line_1, a.address_line_2, a.address_type, a.address_undeliverable, a.address_validated, a.country, a.created_date, a.customer_internal_id, a.postcode, a.region, a.suburb, a.updated_by, a.updated_date, a.updating_store, m.customer_internal_id, m.channel_id, m.created_date, m.opt_in, m.updated_by, m.updated_date, m.updating_store, m.valid_from_date, c1.customer_internal_id, c1.channel_id, c1.type_id, c1.created_date, c1.opt_in, c1.updated_by, c1.updated_date, c1.updating_store, c1.valid_from_date, e.customer_internal_id, e.card_number, e.card_design, e.card_status, e.card_type, e.created_date, e.updated_by, e.updated_date, e.updating_store
FROM customer AS c
LEFT JOIN contact_information AS c0 ON c.customer_internal_id = c0.customer_internal_id
LEFT JOIN address AS a ON c.customer_internal_id = a.customer_internal_id
LEFT JOIN marketing_preferences AS m ON c.customer_internal_id = m.customer_internal_id
LEFT JOIN content_type_preferences AS c1 ON c.customer_internal_id = c1.customer_internal_id
LEFT JOIN external_cards AS e ON c.customer_internal_id = e.customer_internal_id
WHERE c.customer_internal_id IN ('7495c17d-3e53-4808-a991-6c185c4ced7b')
ORDER BY c.customer_internal_id, c0.contact_internal_id, c0.contact_type, a.address_internal_id, m.customer_internal_id, m.channel_id, c1.customer_internal_id, c1.channel_id, c1.type_id, e.customer_internal_id, e.card_number
是否有任何方法通过 EF Core,我可以使用子查询有效地只访问一次数据库?例如
SELECT attribute,
attribute,
attribute,
attribute,
attribute
FROM TABLE
LEFT JOIN OTHER TABLE
LEFT JOIN OTHER TABLE
WHERE customer_internal_id IN (
SELECT customer_internal_id from Customer
WHERE first name = x AND last name = X AND post code = x)
解决方案
var customers = (from customer in loyalty.Customer
.Include(c => c.ContactInformation)
.Include(c => c.Address)
.Include(c => c.MarketingPreferences)
.Include(c => c.ContentTypePreferences)
.Include(c => c.ExternalCards)
.AsNoTracking()
from address in loyalty.Address.Where(a => a.CustomerInternalId == customer.CustomerInternalId)
where customer.FirstName.ToLower() == request.CustomerSearchParameters.FirstName.ToLower()
&& customer.LastName.ToLower() == request.CustomerSearchParameters.LastName.ToLower()
&& address.Address.Postcode == request.CustomerSearchParameters.PostCode
select customer).ToList();
推荐阅读
- swift - Why do I keep getting the error "no such module 'realmswift'"?
- ajax - CodeIgniter Ajax 不会以模态形式填充内容
- sql-server - 哪个是更快的触发器或存储过程?
- javascript - How to combine mergeMap observables calls and return just one value for the whole observable
- postgresql - 查询另一个内的验证并带上过滤后的数据 PostgreSql
- php - 在静态方法中使用会话(cakephp 4 和 3.6+)
- html - 使用表中的固定列和行跨度进行不需要的移位
- java - Android中的电子邮件位图图像
- python - 如何修复此 axios GET 请求?不断收到 404 状态码
- python - Python:捕获对内存中文件的所有写入