none
How to properly test for a missing related table value in Entity Framework 4 RRS feed

  • Question

  • Using ASP.net and Entity Framework 4.0 with LINQ to Entities.

    I have a situation where I have a 1:many relationship set up between two entities. The problem is that in some cases, the related database entry (on the "1" side) does not exist because it was deleted from the database (correctly, by design). The situation is that I need to be able to delete certain records on the "1" side but NOT delete the records on the "many" side (even though they will then no longer point to an existing record on the "1" side).

    So I have an underlying foreign key value on the "many" side that is not null, but points to something that doesn't exist (the key is essentially invalid) since the related entities in the "1" side were deleted. What I would like to do is construct a LINQ query to return the set of entities on the "many" side that do not have valid links to the "1" side. I can't figure this one out.

    So let's say I have two entities, a unique gadget, on the "1" side, and an alert. Gadgets may or may not ever trigger an alert, so there may be zero alert records. A gadget can trigger any number of alerts. At some point in time the gadget record will need to be deleted, but I need to keep the alert records for accounting purposes for 6 months after the gadget goes "inactive" (deleted). There is no need to assocate those alerts back to the original gadget, so no need to keep the gadget in the database in, say, a flagged "deleted" state (besides this causes huge bloat since gadgets come and go quite frequently).

    var data = from alerts in dataContext.alert_table

    where alerts.gadgets == null

    select alerts;

    This query above does not work because the underlying FK's are all non-null - they are keys of records in the gadgets table that used to exist but do not any longer (so obviously I don't have FK constraints enforced or cascade delete, etc.)

    I've tried some tricks like

    where alerts.gadgets.SomeRandomField == null

    thinking if the gadgets record doesn't exist then maybe any of the fields will test as null... this seems so hinky, and if I recall, actually worked in EF 3.5 as a way to test for missing releated entries but doesn't seem to work the same way in EF4 in some cases.

    What is the right way to  do this, to test to see if reference fields with non-null values are referencing things that don't exist? Should I so a subquery?

    var data = from events in dataContext.event_table
    where !dataContext.gadgets.Any(x => x.key == votes.gadgets.key)
    select events;
    
    where the ".key" field is the FK link between the tables. This seems to work but I don't like the fact that I have to do a subquery, isn't there a faster or more direct way to test this? This looks like a weird query but in fact votes.gadgets.key simply returns the value of FK field in event_table, it's obviously not doing an actual check of the gadgets table to see if that record exists.

     


    • Moved by VMazurModerator Thursday, June 23, 2011 10:19 AM (From:ADO.NET Managed Providers)
    • Edited by esassaman Monday, July 18, 2011 9:23 PM
    Wednesday, June 22, 2011 11:42 AM

Answers

  • I apologize for doing a sloppy job on the original problem description. No wonder no one replied to this for so long, lol. I've edited the original question and fixed it up, so again, sorry for the bad explanation in the original form. Can you re-read my original post and see if that makes more sense now?

    The idea of doing a left join is interesting but forcing a lookup like that doesn't seem to get me much better than doing the subquery to see if the related entity actually exists...

    I'm thinking that I've simply been making a bad assumption that relationships in EF4 are always "valid" when in fact, if you are not using cascade delete, or manualy deleting related records, those relationships may be pointing to garbage, so you can't assume in this case that they are valid, ever. So that means... I need to do the subquery to see if it exists.

    • Marked as answer by esassaman Thursday, February 5, 2015 10:43 PM
    Monday, July 18, 2011 10:10 PM

All replies

  • Hi there, maybe the following article will help you:

    http://cbertolasio.wordpress.com/2011/01/20/dealing-with-10-1-relationships-in-entity-framework-4/

    Hope this helps.

    Thanks,


    L.J
    Thursday, June 23, 2011 9:59 AM
  • Sorry, I don't see any connection at all between my question and that article. It doesn't show how to test for the existance of "bad" references to other tables.
    Thursday, June 30, 2011 9:01 AM
  • I am not 100% sure that I understand your question, but I will give it a shot anyway.

    If I understand correctly you have a 1:0..1 relationship, and sometimes no records exist on the 0..1 side?  So an analogy would be 1 aspnetUser may have 0 or 1 UserProfile entity?

    If this is the case, I think you could left join using DefaultIfEmpty and then pick out the records that are set to the DefaultIfEmpty value.

    Not sure if this is supported in EF3.5 

    here is where I got the idea from...

    http://smehrozalam.wordpress.com/2009/06/10/c-left-outer-joins-with-linq/

     [Test]
    [Category("IntegrationTest")]
    public IEnumerable<AspnetUser> GetAspnetUsers_WithMissingProfiles()
    {
        using (var ctx = new MyDataContext()) {

            var query = from aspnetUser in ctx.AspnetUsers
                        from userProfile in ctx.Users.Where(it => it.AspnetUserId == aspnetUser.UserId).DefaultIfEmpty()
                        select new { AspnetUser = aspnetUser, UserProfile = userProfile };

            var queryResult = query.ToList();

            return queryResult.Where(it => it.UserProfile == null).Select(it => it.AspnetUser);
        }
    }


    Saturday, July 16, 2011 6:12 PM
  • I apologize for doing a sloppy job on the original problem description. No wonder no one replied to this for so long, lol. I've edited the original question and fixed it up, so again, sorry for the bad explanation in the original form. Can you re-read my original post and see if that makes more sense now?

    The idea of doing a left join is interesting but forcing a lookup like that doesn't seem to get me much better than doing the subquery to see if the related entity actually exists...

    I'm thinking that I've simply been making a bad assumption that relationships in EF4 are always "valid" when in fact, if you are not using cascade delete, or manualy deleting related records, those relationships may be pointing to garbage, so you can't assume in this case that they are valid, ever. So that means... I need to do the subquery to see if it exists.

    • Marked as answer by esassaman Thursday, February 5, 2015 10:43 PM
    Monday, July 18, 2011 10:10 PM