2

I've been using LinQ to SQL with EF for a while now and have just stumbled on some strange behaviour that hopefully someone may be able to explain for me.

I'm performing a LinQ query on a database context that has a POCO entity with virtual properties for related entities. I'm using a where clause to eliminate instances where that entity is null. I'm using Lazy Loading.

return this.runtimeContext.FatalExceptionLogs.Where(l => l.RuntimeToDesignJuicerRelationship != null);

What I'm finding is that when my query is evaluated LinQ to SQL seems to entirely ignore my condition that the virtual property is null as if I'd never included this check at all. Instead it returns all records in my dbset named FatalExceptionLogs.

Now I have a simple workaround for this which is to first load the data into memory using .ToList() then

This looks like so:

return this.runtimeContext.FatalExceptionLogs.ToList().Where(l => l.RuntimeToDesignJuicerRelationship != null);

Now the check is performed in memory and all instances where the virtual property is null are returned (because there is no corresponding record as the id which is used for the join is nullable) and we're all good.

I have also considered:

  • Checking if the id that is joined on is null but unfortunately I can't garauntee that the referential integrity of the table has been maintained as there is no foreign key applied urgh!.

  • Checking if there are any records in the other table with the matching id, but that could be rather inefficient.

So I have a way of working around this but I'd really like to understand why LinQ to Sql is doing this and what other options there are, can anyone help?

The full code if it helps is below though I've cut it down for this example:

The query:

return this.runtimeContext.FatalExceptionLogs.ToList().Where(l => l.RuntimeToDesignJuicerRelationship != null);

The entity:

public class FatalExceptionLog
{
    public int Id { get; set; }
    public int? RuntimeJuicerId { get; set; }
    public virtual RuntimeToDesignJuicerRelationship RuntimeToDesignJuicerRelationship { get; set; }
}

The mapping: 

public class FatalExceptionLogMap : EntityTypeConfiguration<FatalExceptionLog>
{
    public FatalExceptionLogMap()
    {
        // Primary Key
        this.HasKey(t => t.Id);

        // Table & Column Mappings
        this.ToTable("FatalExceptionLogging");
        this.Property(t => t.RuntimeJuicerId).HasColumnName("JuicerLiveID");

        this.HasRequired(t => t.RuntimeToDesignJuicerRelationship)
            .WithMany(t => t.FatalExceptionLogs)
            .HasForeignKey(t => t.RuntimeJuicerId);
    }
}
DanClarke
  • 23
  • 3
  • Not really sure but does `l.RuntimeToDesignJuicerRelationship.Any()` helps ? – Habib May 15 '14 at 15:59
  • I can't test, but the `RuntimeToDesignJuicerRelationship` relation is tagged as `HasRequired`, which afaik tells linq2sql that it _can't_ be null. In other words, linq2sql may assume you're telling the truth and ignoring the check...? – Joachim Isaksson May 15 '14 at 16:00
  • 1
    Might this apply to you? http://stackoverflow.com/questions/682429/how-can-i-query-for-null-values-in-entity-framework – Silvermind May 15 '14 at 16:02
  • Thanks to each of you for your suggestions, I've had a look at these and done a little experimenting: – DanClarke May 19 '14 at 09:30
  • @Habib thanks but this relationship is to a single instance of RuntimeToDesignJuicerRelationship so .Any isn't available as it's only available on collections – DanClarke May 19 '14 at 09:34
  • @Silvermind this is actually similar but not quite the issue I'm having I've seen this post while searching for an answer but in my instance I'm checking directly for null rather than against a variable so this issue doesn't occur – DanClarke May 19 '14 at 09:34
  • @JoachimIsaksson I believe you've identified the route source of my issue, I missed this as I was expecting there to be a valid relationship in the database and therefore expected it to be required. Changing it to HasOptional will allow me to check that the property is != null and the sql generated will involve a condition to check that the corresponding id (RuntimeJuicerId) is not null. – DanClarke May 19 '14 at 09:36
  • Unfortunately having tried out the suggestion by @JoachimIsaksson the resulting still doesn't join to the table for the RuntimeToDesignJuicerRelationship to verify a record exists so a -1 value in the data will still result in an exception later down the line when attempting to retrieve the data for the RuntimeToDesignJuicerRelationship. At the end of the day I don't think the fault here is with EntityFramework unfortunately I'm having to work with a database a relational database that contains non relation data and this is the source of the issue, I can't expect EF to cover for this. – DanClarke May 19 '14 at 09:40
  • I'm going to leave this up for a few days to see if any other suggestions come up, otherwise my thoughts are the best way to handle this scenario is by making the change suggested by @JoachimIsaksson and then either: 1. Implementing the workaround of performing the check in memory like so: `this.runtimeContext.FatalExceptionLogs.ToList().Where(l => l.RuntimeToDesignJuicerRelationship != null);` 2. Implementing as I originally had it and then catching the exception in the scenario where the value has no corresponding record. – DanClarke May 19 '14 at 09:42
  • Not sure if this would help, but have you looked into `Include()` at all? – Jeff Guillaume May 23 '14 at 14:48
  • Why not check if RuntimeJuicerId has value? – AD.Net May 25 '14 at 18:37
  • @AD.NET: `RuntimeJuicerID` can have a value, but it is NOT gauranteed that value is a valid `RuntimeJuicerID`, so OP is trying to extract those lines where value is NOT valid – sallushan May 25 '14 at 18:58

1 Answers1

1

Why NOT just do the normal joining?

return this.runtimeContext.FatalExceptionLogs.Where(
                    l => runtimeContext.RuntimeJuicers.Any(
                                    y => y.RuntimeJuicerId == l.RuntimeJuicerId
                                                            )
                                                    );
sallushan
  • 1,134
  • 8
  • 16
  • Yes thanks I think this is the correct way of doing this with an explicit join. I think I'd got too into the mindset of using the entities relationships and expected that there should be a way of achieving what I required in that way. So since my database isn't correctly configured with the correct relationships this would be the correct method. I guess depending on performance it may be that the in memory option I defined may become preferable in some scenarios. – DanClarke Jun 19 '14 at 10:54