如何编写涉及多对多表的最小 linq 查询？

发布于 2024-12-19 10:56:25 字数 777 浏览 0 评论 0原文

Product 和 ProductCategory 表通过多对多表 ProductToCategory“连接”。

对于某些具有某个 ID 的产品（让我们将其标记为 product_1），我需要获取与 product_1 相同类别的下一个产品。

我想用 one 查询来完成此操作，因此以下查询可以完成工作，但 sql-profiler 显示非常巨大的查询。有什么建议如何重写查询吗？

(from p in cxt.Products
 join c in cxt.ProductToCategories on p.Id equals c.ProductId
 where p.Id > id && c.CategoryId == (from p2 in cxt.Products
                                     join c2 in cxt.ProductToCategories on p.Id equals c.ProductId
                                     where p2.Id == id
                                     select c2.CategoryId).FirstOrDefault()
 orderby p.Id
 select p).FirstOrDefault();

原文

Product and ProductCategory tables are "connected" via many-to-many table ProductToCategory.

For some product with some id(let's mark it as product_1) I need to get the next product of the same category as the product_1.

I wanted to do this with one query, so the following query does the work but the sql-profiler shows very HUGE query. Are there any suggestions how rewrite the query?

(from p in cxt.Products
 join c in cxt.ProductToCategories on p.Id equals c.ProductId
 where p.Id > id && c.CategoryId == (from p2 in cxt.Products
                                     join c2 in cxt.ProductToCategories on p.Id equals c.ProductId
                                     where p2.Id == id
                                     select c2.CategoryId).FirstOrDefault()
 orderby p.Id
 select p).FirstOrDefault();

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晒暮凉 2024-12-26 10:56:25

注意：我想什么时候说

我需要获取与product_1同一类别的下一个产品。

真正的意思是

我需要获取 Product_1 类别之一的下一个产品。

(from p in ctx.Products
join c in cts.ProductsToCategories on c.ProductId 
join c2 in cts.ProductsToCategories on c.CategoryId = c2.CategoryId 
join p2 in ctx.Products on c2.ProductId = p2.id
where 
  p2.Id == id &&
  p2 != p
orderby p).FirstOrDefault();

Note: I suppose that when is said

I need to get the next product of the same category as the product_1.

what really means is

I need to get the next product of the one of categories of product_1.

(from p in ctx.Products
join c in cts.ProductsToCategories on c.ProductId 
join c2 in cts.ProductsToCategories on c.CategoryId = c2.CategoryId 
join p2 in ctx.Products on c2.ProductId = p2.id
where 
  p2.Id == id &&
  p2 != p
orderby p).FirstOrDefault();

回复收藏 0 原文