How to write a expression for a linq to sql property?

Question

My appologies upfront for the lengthy question. I made quite an effort to make my question as clear as possible in one go. Please bear with me. ;o) any help will be greatly appreciated!

I have the classes Branch and Text:

class Branch
 int ID
 Text WebDescription
 and a bunch of other properties

class Text
 int ID
 string UK
 string NL
 string FR
 string IT
 and a bunch of other properties as well

I want to only display the ID of the branch and its description in the appropriate language. I want only one query (no extra round trips) which retrieves only two fields (not the whole object).

I found three solutions

Via the object model in the query

// good: no round trips
// good: clean sql
// bad:  impossible to use the currentUserLanguage parameter
var lang = "NL";
var dbProject = new ProjectDataContext();
var query = from b in dbProject.GetTable<Branch>()
            select new
               {
                 b.ID,
                 WebDescription = b.WebDescriptionObject.NL // <-- problem
               };
var text = query.First().WebDescription;

Via the object model after the query

// good: no round trips (eager loading of text object)
// good: possible to use the currentUserLanguage parameter
// bad:  loads the *whole* branch and text object, not just two fields
var lang= "NL";
var dbProject = new ProjectDataContext();
var query = from b in dbProject.GetTable<Branch>()
            select new
              {
                b.ID,
                WebDescription = b.GetWebDescriptionAsString(lang)
              };
var text = query.First().WebDescription;

Using an expression

// good: I have the feeling I am on the right track
// bad: This doesn't work :o( throws an exception
var lang= "NL";
var dbProject = new ProjectDataContext();
var query = from b in dbProject.GetTable<Branch>()
            select new
              {
                b.ID,
                WebDescription = b.GetWebDescriptionAsExpression(lang)
              };
var text = query.First().WebDescription;

Here is code for the two methods GetWebDescriptionAsString and GetWebDescriptionAsExpression.

public string GetWebDescriptionAsString(string lang)
{
  if (lang== "NL") return WebDescriptionObject.NL;
  if (lang== "FR") return WebDescriptionObject.FR;
  if (lang== "IT") return WebDescriptionObject.IT;
  return WebDescriptionObject.UK;
}

public Expression<Func<Branch, string>> GetWebDescriptionAsExpression(string lang)
{
  if (lang== "NL") return b => b.WebDescriptionObject.NL;
  if (lang== "FR") return b => b.WebDescriptionObject.FR;
  if (lang== "IT") return b => b.WebDescriptionObject.IT;
  return b => b.WebDescriptionObject.UK;
}

Answer 1

This would be very easy to do if you used a stored procedure. Are you opposed to using SP's as a solution?

Answer 2

If a stored procedure works, then I am happy to use it.

Answer 3

Without really answering the question, the cleanest approach would be to change the Text structure into a more normalized form like:

Text
    ID

TextTranslation
    ID
    TextID
    Lang
    TextValue

where each text has a number of translations, one for each language.

The query would become something like:

var q = 
    from branch in dbProject.Branches
    join text in dbProject.Texts on branch.TextID = text.ID
    join translation in dbProject.TextTranslations on text.ID = translation.TextID
    where translation.Lang == lang
    select new
    {
        branch.ID,
        WebDescription = translation.TextValue
    };

This approach has other advantages as well, for example adding a new language will not change the model structure.

Answer 4

Without understanding your whole problem

create a stored procedure like this:

CREATE PROCEDURE spGetTheTextINeed @Language char(2), @BranchID int
AS
  /* I don't know how your database is structured so you need to write this */
  SELECT MyText from MyTable WHERE Language=@Language and Branch=@BranchID

Then you need to add the sp to your DBML and then you can just call the sp you need with the appropriate parameters:

var query = myDataContext.spGetTheTextINeed("NL",[your branch number]) 

  Dim str As String 
  str = query.MyText

The code above is not to be exact - I don't understand your full requirements but this should get you started.

Answer 5

Thank you for you prompt reply.

I made a quick attempt. The UDF was already there, I just didn't know how to use it. The performance dropped significantly. The first solution is 3 times faster. In my understanding, this approach would require extra round trips to the database. Is that correct?

var query = from b in dbProject.GetTable<Branch>()
            select new
              {
                b.ID,
                WebDescription = db.fGetText(b.WebDescriptionID, (currentUserLanguage))
              };