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

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 开发者_JAVA技巧nvarchar(200)

LangID Int

---

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: 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.

---

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