Search code examples
sql.netentity-frameworknullable

SQL View returns null on a EF not null field

I am using .NET 4.5.2, EF6 and SQL2016. I have a sql view that pulls event information

CREATE VIEW [dbo].[vVisitEvent]
AS
SELECT
ev.[GUID] as EventID,
vt.[GUID] as VisitorID,
cr.Name as CountryName
FROM [Event] ev
  INNER JOIN (
  [Location] lc 
  LEFT JOIN [Country] cr ON lc.CountryGUID = cr.GUID
) ON ev.LocationGUID = lc.GUID

The vVisitEvent.cs under DataModel.tt is like this:

public string CountryName{get;set;}

Some locations do not have a CountryGUID so CountryName field would be null. I used LEFT JOIN and it ran properly in SQL studio. It returned all events with CountryName null if the event location has no CountryGUID. However, I got the following error in Visual Studio and it seems to be caused by the null CountryName:

   System.NullReferenceException: Object reference not set to an instance of an object.
   at System.Data.Entity.Core.EntityKey.AddHashValue(Int32 hashCode, Object keyValue)
   at System.Data.Entity.Core.EntityKey.GetHashCode()
   at System.Collections.Generic.GenericEqualityComparer`1.GetHashCode(T obj)
   at System.Collections.Generic.Dictionary`2.FindEntry(TKey key)
   at System.Collections.Generic.Dictionary`2.TryGetValue(TKey key, TValue& value)
   at System.Data.Entity.Core.Objects.ObjectStateManager.TryGetEntityEntry(EntityKey key, EntityEntry& entry)
   at System.Data.Entity.Core.Objects.ObjectStateManager.FindEntityEntry(EntityKey key)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
   at lambda_method(Closure , Shaper )
   at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Any ideas or possible solutions? Thanks!

Solution

  • I figured out. It is db first and when vVisitEvent is added to edmx (update from database) somehow EF thinks CountryName is a DB Key and put it under <key> I had to manually uncheck 'db key' in edmx diagram