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.
The tables that are used
Product contains a unique id for each product. This column is called 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).
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.
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?