In LINQtoSQL, how can I update child relationships by editing the parent model?

Question

Hello SO,

This is my first attempt at updating a database using LINQtoSQL. At least, it's my first if you don't count the tutorials I've followed. Unfortunately, The tutorials that I have found don't offer much more than updating a single table. I'm attempting to update a DB Model that's a bit more complex.

I have a Stream table:

[Table]
public class Stream
{
    [HiddenInput(DisplayValue = false)]
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public long StreamID { get; set; }

    /** Other columns removed for brevity **/

    // relationship:
    private EntitySet<Stream2FieldTypes> _Stream2FieldTypes;
    [System.Data.Linq.Mapping.Association(Storage = "_Stream2FieldTypes", OtherKey = "StreamID")]
    public EntitySet<Stream2FieldTypes> Stream2FieldTypes
    {
        get { return this._Stream2FieldTypes; }
        set { this._Stream2FieldTypes.Assign(value); }
    }

And I have a Stream2FieldTypes table:

[Table]
public class Stream2FieldTypes
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public long s2fID { get; set; }
    public long StreamID { get; set; }     // FK

    /** other columns removed for brevity **/

    // relationship (one Stream2FieldTypes to many Streams) 
    private EntitySet<Stream> _Stream;
    [Association(Storage = "_Stream", ThisKey = "StreamID")]
    public EntitySet<Stream> Stream
    {
        get { return this._Stream; }
        set { this._Stream.Assign(value); }
    }

Now, I am trying to update the model so I can send updates to the repository to persist to DataContext. I can't update Stream.Stream2FieldTypes because the get is set to a private EntitySet.

How do I update Stream.Stream2FieldTypes when I can't change Stream.Stream2FieldTypes because it's a private EntitySet<>?

Edit: psuedo code

Basically, I think I should be able to update the Stream and Stream2FieldTypes tables by using a command in my Edit Action like this:

myRepository.SaveStream(stream);

I have been trying to do something like this:

        if (ModelState.IsValid)
        {
            // Convert StreamEditModel to Stream
            var stream = new Genesis.Domain.Entities.Stream
            {
                StreamID = form.StreamID,
                StreamUrl = form.StreamUrl,
                StreamName = form.StreamName,
                StreamBody = form.StreamBody,
                StreamTitle = form.StreamTitle,
                StreamKeywords = form.StreamKeywords,
                StreamDescription = form.StreamDescription,
                Stream2FieldTypes = new EntitySet<Stream2FieldTypes>()
            };

            // Loop to convert Stream2FieldTypes to Steam2FieldTypesEditModel
            foreach (var item in form.Stream2FieldTypes)
            {
                var fieldTypeEntry = new Stream2FieldTypes
                {
                    FieldTypeID = item.FieldTypeID,
                    s2fID = item.s2fID,
                    s2fIsRequired = item.s2fIsRequired,
                    s2fLabel = item.s2fLabel,
                    StreamID = item.StreamID,
                };
                stream.Stream2FieldTypes.Add(fieldTypeEntry); // Add to list
            }

            genesisRepository.SaveStream(stream);

            return RedirectToAction("Index");
        }
        else 
        {
            return View(form);
        }

When I try to run this code, I get this error:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 58: {

Line 59: get { return this._Stream2FieldTypes; }

line with error: Line 60: set { this._Stream2FieldTypes.Assign(value); }

Line 61: }

Line 62:

Answer 1

In your SaveStream method, you set the StreamID property like this StreamID = item.StreamID. Because you have a Stream id I expect you are trying to change an existing Stream class. However, when looking at your code, you are creating a new one. I think the problem you are experiencing is caused by that.

What you should do is the following:

• Differentiate between creating a new one and mutating an existing entity.
• Try to minimize the use of ID properties as much as you can. Work with the entities itself. This makes your code much cleaner and readable. (I always make the ID properties internal and the EntitySet and EntityRef public).

For instance, I think your SaveStream should look more like the next code. If you look closely, this code lacks lines such as:

• FieldTypeID = item.FieldTypeID and
• StreamID = form.StreamID.

This is because this simply wont work. You need to retrieve an existing entity from the database first and update it. You can't create a new object, set its ID to an existing record in the database and expect LINQ to SQL to update that record for you. This is not how LINQ to SQL is designed.

Here is an example of what might work for you:

public void SaveStream(StreamEditModel stream)
{
    if (!ModelState.IsValid)
    {
        return;
    }

    if (stream.Id == 0)
    {
        CreateStream(stream);
    }
    else
    {
        UpdateStream(stream);
    }
}

private void CreateStream(StreamEditModel form)
{
    var stream = new Stream();

    FillStream(stream, form);

    UpdateStream2FieldTypes(stream, form);

    genesisRepository.SubmitChanges();
}

private void UpdateStream(StreamEditModel form)
{
    var stream = genesisRepository.GetById(stream.StreamID);

    FillStream(stream, form);

    UpdateStream2FieldTypes(stream, form);

    genesisRepository.SubmitChanges();
}

private void FillStream(Stream stream, StreamEditModel form)
{
    stream.StreamUrl = form.StreamUrl;
    stream.StreamName = form.StreamName;
    stream.StreamBody = form.StreamBody;
    stream.StreamTitle = form.StreamTitle;
    stream.StreamKeywords = form.StreamKeywords;
    stream.StreamDescription = form.StreamDescription;
}

private void UpdateStream2FieldTypes(Stream stream,
    StreamEditModel form)
{
    var typesToDelete =
        from type in stream.Stream2FieldTypes
        let ids = form.Stream2FieldTypes.Select(t => t.FieldTypeID)
        where !ids.Contains(type.FieldTypeID)
        select type;

    genesisRepository.RemoveStream2FieldTypes(typesToDelete);

    var typesToAdd =
        from type in form.Stream2FieldTypes
        where type.FieldTypeID == 0
        select CreateStream2FieldTypes(type);

    foreach (var typeToAdd in typesToAdd)
    {
        stream.Stream2FieldTypes.Add(typeToAdd);
    }

    var formTypesToUpdate = 
        from type in form.Stream2FieldTypes
        where type.FieldTypeID != 0
        select type;

    foreach (var modelToUpdate in formTypesToUpdate)
    {
        var typeToUpdate = stream.Stream2FieldTypes.Single(
            t => t.FieldTypeID == modelToUpdate.FieldTypeID);

        FillStream2FieldTypes(typeToUpdate, typeToUpdate);
    }
}

private static  Stream2FieldTypes CreateStream2FieldTypes(
    Stream2FieldTypesEditModel form)
{
    var fieldType = new Stream2FieldTypes();

    FillStream2FieldTypes(fieldType, form);

    return fieldType;
}

private static void FillStream2FieldTypes(
    Stream2FieldTypes type, 
    Stream2FieldTypesEditModel item)
{
    type.s2fID = item.s2fID;
    type.s2fIsRequired = item.s2fIsRequired;
    type.s2fLabel = item.s2fLabel;
}

Cheers