Save Null value in SQL Server via Entity Framework

Question

I have a table in which I need to insert Null value. Inside "FamilyTreeMember" table I have a column called "BirthMonth_Id" which is a FK to another table called BirthMonthDD. BirthMonthDD is my lookup table which I use it to generate DropDownList items.

In my current setup, I need to allow the end user to leave that field blank and save it as Null but it doesn't happen that way and instead it returns the old/same value. I don't get any error message. I can easily update the value from one thing to another. Example from February to August and ...

How can I fix this?

Model

public class FamilyTreeMember
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public Guid FamilyTreeMemberId { get; set; }

        public int? BirthDay { get; set; }
        public virtual MonthDD BirthMonth { get; set; }
        public int? BirthYear { get; set; }
        public bool DOBEstimated { get; set; }       
    }

ViewModel

public class EditFamilyTreeMemberViewModel
    {
        [ScaffoldColumn(false)]
        public string CaseId { get; set; }

        [ScaffoldColumn(false)]
        public string FamilyTreeMemberId { get; set; }


        [UIHint("Day")]
        [RegularExpression(@"([0-9]+)", ErrorMessage = "The {0} must be a number.")]
        [Display(Name = "Day Of Birth")]
        public int? BirthDay { get; set; }

        [UIHint("ComboBox")]
        [AdditionalMetadata("BindTo", "months")]
        [Display(Name = "Month Of Birth")]
        public int? BirthMonth { get; set; }


        [UIHint("Year")]
        [RegularExpression(@"([0-9]+)", ErrorMessage = "The {0} must be a number.")]
        [Display(Name = "Year Of Birth")]
        public int? BirthYear { get; set; }

        [UIHint("Bool")]
        [Display(Name = "DOB Estimated")]
        public bool DOBEstimated { get; set; }
    }

Controller (Update method)

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Edit(EditFamilyTreeMemberViewModel editfamilytreemember, Guid? ftm, Guid? cid, Guid? mid)
{
    ViewBag.ftm = ftm;
    ViewBag.cid = cid;
    ViewBag.mid = mid;

    if (ModelState.IsValid)
    {
        if (ftm == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.NotFound);
        }
        using (db)
        {
            var ftmember = await db.FamilyTreeMember.SingleOrDefaultAsync(z => z.FamilyTreeMemberId == ftm);

            ftmember.BirthDay = editfamilytreemember.BirthDay;
            ftmember.BirthMonth = await db.MonthDD.SingleOrDefaultAsync(x => x.Id == editfamilytreemember.BirthMonth);
            ftmember.BirthYear = editfamilytreemember.BirthYear;
            ftmember.DOBEstimated = editfamilytreemember.DOBEstimated;

            await db.SaveChangesAsync();
        }
        return RedirectToAction("Index", "FamilyTree", new { cid, mid });
    }
    return View();
}

Use ftmember.BirthMonth_Id = editfamilytreemember.BirthMonth; and you data model should have a property public MonthDD int BirthMonth_Id { get; set; } — user3559349
– user3559349, Commented Apr 15, 2016 at 3:44
Can and should the Birthday component be null? That's the question you've got to ask yourself. You would want to try and reduce the amount of nulls you are using.... Try looking at the null object pattern — Callum Linington
– Callum Linington, Commented Apr 15, 2016 at 11:34

Jcl · Accepted Answer · 2016-04-15 11:38:05Z

2

The problem is that you can't break a relationship that has not been loaded before. So either Include the relationship when you query for your member:

var ftmember = await db.FamilyTreeMember.Include(x => x.BirthMonth).SingleOrDefaultAsync(z => z.FamilyTreeMemberId == ftm);

Or load this relationship before you set it to null:

db.Entry(ftmember).Reference(x => x.BirthMonth).Load();

This problem arises becase the property is null before you assign it (since the relationship hasn't been loaded), so when you assign null to it, it doesn't mark it as modified.

edited Apr 15, 2016 at 11:38

answered Apr 15, 2016 at 3:50

Jcl

28.4k5 gold badges66 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

CodeMaster Over a year ago

Thanks, I started by using the last part of your question and I get the following error:"The property 'BirthMonth' from the property path 'BirthMonth' is not a complex property on type 'FamilyTreeMember'. Property paths must be composed of complex properties for all except the final property."

Jcl Over a year ago

@NoMore_CodeMaster ouch, that is right, that won't work for references... you'd need to find the mapping (and that's getting into the ObjectContext context which I don't recommend). See my update (using Reference and IsLoaded instead of Property and IsModified): again, untested, it may not work at all

CodeMaster Over a year ago

I tried this one: db.Entry(ftmember).Reference(x => x.BirthMonth).Load(); and it worked, I'll also try the first one as well. I'll keep you posted.

Jcl Over a year ago

Yeah, loading should work... the last method just avoids the loading from the database if you don't need it (would save a more expensive query in the case of Include and a database roundtrip in the case of Load). The last untested method aims at "cheating" entity framework to avoid those... but if that doesn't work (again, haven't tested), then it's unavoidable in a simple manner

CodeMaster Over a year ago

Ok, the first two solutions work well, which one do think is better to use?

|

Collectives™ on Stack Overflow

Save Null value in SQL Server via Entity Framework

Model

ViewModel

Controller (Update method)

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Model

ViewModel

Controller (Update method)

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related