entity framework when many to many is holding data

Question

I'm using Entity Framework CTP5.

I have a schema like this:

• A group contains many textdescriptions.
• A textdescriptions has many texts.
• A Language has many texts.

So there are 4 tables. Groups one-to-many DEscriptions many-to-many Texts many-to-one Languages.

So I have a many-to-many relationship where the relation also holds data.

Definitions of Text and TextDescription ( since we can query on the Id for Group and Languages I havent added them here )

public class Text
{
    public int TextID { get; set; }
    public int TextDescriptionID { get; set; }
    public int LanguageID { get; set; }
    public string OriginalText { get; set; }
    public bool IsValid { get; set; }
    public DateTime Added { get; set; }
    public DateTime Updated { get; set; }
    public Language Language { get; set; }
    public TextDescription TextDescription { get; set; }

    public static Text GetMissingText(string input)
    {
        Text text = new Text();
        text.OriginalText = "Missing: " + input;
        text.IsValid = true;
        text.TextDescription = new TextDescription()
                               {
                                   IsStatic = true,
                                   Name = input,
                                   IsMultiline = false,
                               };

        return text;
    }
}

public class TextDescription
{
    public int TextDescriptionId { get; set; }
    public int TextDescriptionGroupId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public bool UseHtml { get; set; }
    public bool IsMultiline { get; set; }
    public bool IsStatic { get; set; }

    public TextDescriptionGroup TextDescriptionGroup { get; set; }
    public virtual ICollection<Text> Texts { get; set; }

    public static TextDescription GetNewItem(int textDescriptionGroupId)
    {
        var item = new TextDescription();
        item.Name = item.Description = "n/a";
        item.UseHtml = item.IsMultiline = item.IsMultiline = false;
        item.TextDescriptionGroupId = textDescriptionGroupId;
        return item;
    }
}

When adding either a new language or a new text is inserted ... the many to many relation is not inserted into the database. (Think it would be a bad idea, so in the end, if thats the only solution, I could be able to that)

So how do I handle this in a smart way when I need to fetch all the text for a specific group from the database, but also get the translation if there are one for that languages.

I can't start fra the translation object, since its possible its not there. If I start to query from the Text entity ... how do I only select one language without getting all languages first.

 repo.Find(x => 
           x.GroupId == groupId && 
           x.Translation.Any(a => a.LanguageID == id.Value)
 );

I'm lost here ... any there any smart way ... so I wont have to query the database for all the Texts ... and then a query for each item ... to see if there are a translation? or else just make a new empty one.

In SQL I would do it like this:

SELECT TD.Name, T.OriginalText FROM TextDescriptions TD
LEFT JOIN Texts T ON TD.TextDescriptionId = T.TextDescriptionId
WHERE TextDescriptionGroupId = 41 AND ISNULL(T.LanguageId, 1) = 1

The above SQL will give me the elements even if there is not record now, I get a NULL for these values. I could then handle that it my code and avoid lazy load.

But can I get the same behavior in Entity Framework. I could see there would be some problems maybe for EF4 to do the mapping ... since I'm going from TextDesciptions to Texts ... and TextDesciptions have a List of Texts ... but here ... I only want either 1 or NULL, or just a new Entity that havent been added to the database yet.

Looking forward to some interesting answers.

mvh

Answer 1

For now ... if no other solution is found I will be running the follow SQL script to insert empty records. This way I'm sure the record is there when a user wants to edit it and dont have to ensure its there before saving it. Maybe also avoiding some naste Linq query.

I only have to run this SQL 2 places. When adding a new Language or new a new TextDesciption.

INSERT INTO Texts 
SELECT TD.TextDescriptionId, L.LanguageId, '', 0, GETDATE(), GETDATE(), L.TwoLetterISOLanguageName 
FROM TextDescriptions TD 
INNER JOIN Languages L ON 1 = 1 
LEFT JOIN Texts T ON 
T.TextDescriptionId = TD.TextDescriptionId AND 
T.LanguageId = L.LanguageId 
WHERE TextId IS NULL