Linq to Sql : Overwrite properties while Joining tables

Question

I'm trying to overwrite an object by values of another table depending the culture. I would like to keep the original object-type. I already tried different approach but always ending up with a non-supported-exception or a casting error.

Example Code:

What I wanted to retrieve is the original object with "Name" and the "Text" overwritten by the Contact_tls table like

1. this will return me a list of the original Contacts, not translated.
2. this will return me a list of translated items as I want but as an Anonymous typelist.
3. Possible, but you have to re-assign all non-language related properties like (ID's, ApplicationID, PhoneNumber, etc)

4. Last approach, It should be something similar ( throwing errors )

   protected void Page_Load(object sender, EventArgs e) { List Contacts = new List(); webDataContext db = new webDataContext();

   //1
   Contacts = db.Contacts.Join(
       db.Contact_tls.Where(i => i.Culture == "fr"),
       i => i.ID,
       t => t.ID,
       (i, t) => i).ToList();
   
   //2
   var linqObject = db.Contacts.Join(
       db.Contact_tls.Where(i => i.Culture == "fr"),
       i => i.ID,
       t => t.ID,
       (i, t) => new { ID = i.ID, Name = t.Name, Text = t.Text }).ToList();
   
   //3
   Contacts = db.Contacts.Join(
       db.Contact_tls.Where(i => i.Culture == "fr"),
       i => i.ID,
       t => t.ID,
       (i, t) => new Contact { ID = i.ID, Name = t.Name, Text = t.Text }).ToList();
   
   //4  
   var contacts = db.Contacts.Join(
       db.Contact_tls.Where(i => i.Culture == "fr"),
       i => i.ID,
       t => t.ID,
       (i, t) => i { Name = t.Name, Text = t.Text }).ToList();
   

   }

Answer 1

I would create the Class using Linq to Objects, so you could modify #2 as follows:

Contacts = db.Contacts.Join(
    db.Contact_tls.Where(i => i.Culture == "fr"),
    i => i.ID,
    t => t.ID,
    (i, t) => new { ID = i.ID, Name = t.Name, Text = t.Text })
    .AsEnumerable()
    .Select(x =>  new Contact { ID = x.ID, Name = x.Name, Text = x.Text })
    .ToList();

Answer 2

Try adding a constructor to the Contract class with a parameter per property and use it with number 3:

Contacts = db.Contacts.Join(db.Contact_tls.Where(i => i.Culture == "fr"),
                            i => i.ID, t => t.ID,
                            (i, t) => new Contact(i.ID, t.Name, t.Text))
                      .ToList();