MVC 2, Entity Framework 4. Custom Generic Query. Where I put the code?

Question

Here is my problem, I have a multilang database shema. First I want a query with this result
French | English
Car | NULL
Etoile | Stars
NULL | Monkey
...

I'm pretty sure I will need to use A left join combinate with Pivot table.
Where I'm suppose to put this query. In a Partial class, or I need to use the Repository Pattern to hold the query.

I Have a lot of multilang table in the database. How can i make a generic query

Languages
LangID PK
LangName nvarchar(100)

Category
CatID Pk
IsActive Bit

CategoryText
CatID FK
CatName nvarchar(200)
LangID Int

Answer 1

A simple query which procduces your result (assume LangID1 = french, LangID2 = english):

SELECT ct1.CatName as french, ct2.CatName as english
FROM CategoryText ct1, CategoryText ct2
WHERE ct1.LangID = 1 AND ct2.LangID = 2
AND ct1.CatID = ct2.CatID

In which scenario do you need the multilanguage ability? If you need only 2 languages at runtime, i suggest you to query both and build a Tuple (the new Tuple class comes with .NET 4.0) Tuple where the first generic parameter is the lang id and the others the specific languages. You could build a partial class on your CategoryName entity, which holds a function, which provides you those dictionary/tuple . You can easily translate the above query into LINQ e.g.

Jan

Answer 2

It seems like you're asking several questions, here. First, when you ask where to put the query, that's really up to the design you're following. A repository pattern seems like a reasonable idea for data access in a lot of cases, but I've no idea if it's appropriate or not in your case. Are you already using repositories elsewhere, for example?

Second, are you looking for a working, "generic" query (ie. one that will work for any number of languages) in SQL, or through LINQ?

If in LINQ, please take a look at the answers to this question. They might be applicable: http://stackoverflow.com/questions/963491/pivot-data-using-linq

If in SQL, and assuming you're using SQL Server, you can indeed perform a pivot either with GROUP BY or, if you're running a recent version, the PIVOT operator. However, neither case supports a dynamic number of languages - the requested columns for each must be declared explicitly. Like so:

--GROUP BY
SELECT ct.CatID,
  MAX(CASE WHEN l.LangName = 'English' THEN ct.CatName END) AS English,
  MAX(CASE WHEN l.LangName = 'French' THEN ct.CatName END) AS French
FROM CategoryText ct INNER JOIN Languages l ON l.LangID = ct.LangID
GROUP BY ct.CatID;

--PIVOT
SELECT ct.CatID, English, French
FROM CategoryText ct INNER JOIN Languages l ON l.LangID = ct.LangID
  PIVOT(MAX(ct.CatName) FOR l.LangName
    IN([English], [French])) AS P;

Since the Pivot operator doesn't support a dynamic statement like a subquery in the spreading argument (IN(English, French)), I'm not sure how to handle the generic case. Hopefully this will at least clarify your direction.