通过优化Linq To SQL查询删除多个子查询（包含连接）的建议

I'm working on adding globalization to my product cataloge and I have made it work. However, I feel that the underlaying SQL query isn't performing as well as it could and I could need some advice on how to change my Linq To SQL query to make it more efficient.

我正在努力为我的产品目录添加全球化,并且我已经使它成功了。但是,我觉得底层SQL查询的性能不尽相同,我可能需要一些关于如何更改Linq To SQL查询以使其更高效的建议。

The tables that are used

使用的表

Product contains a unique id for each product. This column is called EntityID

产品包含每种产品的唯一ID。此列称为EntityID

TextTranslation contains the globalized text. The columns in this table are CultureID (a string), TextID (a reference to the text), Value (the actuall globalized text).

TextTranslation包含全球化文本。此表中的列是CultureID(字符串),TextID(对文本的引用),Value(实际全球化文本)。

Text contains the mapping between a globalized text and a product. There is also a column which indicates which type of text it is (like name, description and so on)

文本包含全球化文本和产品之间的映射。还有一个列指示它是哪种类型的文本(如名称,描述等)

TextType contains the definition (id, name and description) for a text type.

TextType包含文本类型的定义(id,名称和描述)。

var culturedTexts =
  from translation in ctx.TextTranslations
  join text in ctx.Texts on translation.TextId equals text.TextId
  where translation.CultureId == "en-EN"
  select new
  {
    text.EntityId,
    text.TextTypeId,
    translation.Value,
  };

var products =
  from p in ctx.Products
  let texts = culturedTexts.Where(i => i.EntityId == p.EntityId)
  select new Model.Product
  {
    Description = texts.Where(c => c.TextTypeId == (int)TextType.Description).SingleOrDefault().Value,
    Name = texts.Where(c => c.TextTypeId == (int)TextType.Name).SingleOrDefault().Value
  };

When this is executed I get a query which looks like

当执行此操作时,我得到一个看起来像的查询

SELECT (
    SELECT [t1].[Value]
    FROM [Common].[TextTranslation] AS [t1]
    INNER JOIN [Common].[Text] AS [t2] ON [t1].[TextId] = [t2].[TextId]
    WHERE ([t2].[TextTypeId] = 2) AND ([t2].[EntityId] = [t0].[EntityId]) AND ([t1].[CultureId] = 'sv-SE')
    ) AS [Description], (
    SELECT [t3].[Value]
    FROM [Common].[TextTranslation] AS [t3]
    INNER JOIN [Common].[Text] AS [t4] ON [t3].[TextId] = [t4].[TextId]
    WHERE ([t4].[TextTypeId] = 1) AND ([t4].[EntityId] = [t0].[EntityId]) AND ([t3].[CultureId] = 'sv-SE')
    ) AS [Name]
FROM [Catalog].[Product] AS [t0]

So each globalized text (Name, Description) in the LINQ query gets its own sub query and associated join. Is it possible to streamline this a bit and remove each text type getting its own join and subquery?

因此,LINQ查询中的每个全球化文本(名称,描述)都有自己的子查询和关联的连接。是否有可能简化这一点并删除每个文本类型获取自己的连接和子查询?

1 个解决方案

#1

Well, given that they are getting different TextTypeId values, how would you prefer the TSQL to look? If you do a single JOIN, you'll have to put in a messy SELECT CASE or similar to discriminate between type "1" and type "2".

好吧,鉴于他们获得了不同的TextTypeId值,您希望TSQL看起来如何?如果你做一个JOIN,你将不得不放入一个凌乱的SELECT CASE或类似的东西来区分类型“1”和类型“2”。

One option would be to to simply bring back all the suitable rows and do the final projection in-memory at the client, but to be honest I expect that the SQL optimizer will make light work of that TSQL anyway... especially if that query hits a good spanning index.

一种选择是简单地返回所有合适的行并在客户端进行内存中的最终投影,但说实话,我希望SQL优化器无论如何都能轻松完成TSQL ...特别是如果查询达到了良好的跨越指数。