I am trying to construct a query that will allow me to add an expression that will return only contacts that are/are not associated with a person.
The base IQueryable looks like this ...
internal static IQueryable<OrganizationContact> GetOrganizationContactQuery(IFDBCtx ctx)
{
var query = from rContact in ctx.Contacts.Where(e => e.institution_id.HasValue)
join rPerson in ctx.People
on rContact.person_id equals rPerson.person_id into peopleGroup
from person in peopleGroup.DefaultIfEmpty()
select new BLL.OrganizationContact
{
ID = rContact.contact_association_id,
Title = rContact.contact_title,
Notes = rContact.notes,
CreatedBy = rContact.created_by,
CreatedDate = rContact.created_date,
UpdatedBy = rContact.updated_by,
UpdatedDate = rContact.updated_date,
ContactName = (person == null) ? null : person.name,
Person = (person == null) ? null : new BLL.Person { ID = person.person_id, Name = person.name }
}
return query;
}
This, however throws the exception ...
Unable to create a null constant value of type 'BLL.Person'. Only entity types, enumeration types or primitive types are supported in this context.
When I remove the person == null evaluation from the Person assignment the exception goes away ...
internal static IQueryable<OrganizationContact> GetOrganizationContactQuery(dbContext ctx)
{
var query = from rContact in ctx.Contacts.Where(e => e.institution_id.HasValue)
join rPerson in ctx.People
on rContact.person_id equals rPerson.person_id into peopleGroup
from person in peopleGroup.DefaultIfEmpty()
select new BLL.OrganizationContact
{
ID = rContact.contact_association_id,
Title = rContact.contact_title,
Notes = rContact.notes,
CreatedBy = rContact.created_by,
CreatedDate = rContact.created_date,
UpdatedBy = rContact.updated_by,
UpdatedDate = rContact.updated_date,
ContactName = (person == null) ? null : person.name,
Person = new BLL.Person { ID = person.person_id, Name = person.name }
}
return query;
}
But now I am left with another problem. When I attempt to evaluate to see if the Person is set or not ...
var query = GetOrganizationContactQuery(dbContext);
query = query.Where(e=>Person == null);
I get another exception ...
The argument to DbIsNullExpression must refer to a primitive, enumeration or reference type.
In an effort to work around the issues I attempt to evaluate the ContactName instead (it's a primitive type after all).
var query = GetOrganizationContactQuery(dbContext);
query = query.Where(e=>ContactName == null);
This does not throw an exception, but instead returns EVERY record in the table. As it turns out, the ternary expression, (person == null), appears to ALWAYS evaluates to NULL, even when the Name of the Person object property is correctly set! Here's an example of this output. You can see that the Person object was correctly set, but the ContactName is not present (properties with NULL values are not included in the JSON).
{
"ID": 2467,
"Title": "CEO",
"CreatedBy": "gstenstrom",
"CreatedDate": "2019-05-21T17:37:18.997",
"UpdatedBy": "gstenstrom",
"UpdatedDate": "2019-05-21T17:37:18.997",
"Person": {
"ID": 2610,
"Name": "John Jones",
}
}
So I cannot evaluate the Person object property and I cannot set the ContactName ... I cannot filter out the contacts that do or do not have People associated with them!
This is a pretty basic query seemingly made difficult by Linq. Am I missing something?
Upon further research I have discovered that this works ...
internal static IQueryable<OrganizationContact> GetOrganizationContactQuery(dbContext ctx)
{
var query = from rContact in ctx.Contacts.Where(e => e.institution_id.HasValue)
join rPerson in ctx.People
on rContact.person_id equals rPerson.person_id into peopleGroup
from person in peopleGroup.DefaultIfEmpty()
select new BLL.OrganizationContact
{
ID = rContact.contact_association_id,
Title = rContact.contact_title,
Notes = rContact.notes,
CreatedBy = rContact.created_by,
CreatedDate = rContact.created_date,
UpdatedBy = rContact.updated_by,
UpdatedDate = rContact.updated_date,
ContactName = person.name,
Person = new BLL.Person { ID = person.person_id, Name = person.name }
}
return query;
}
var query = GetOrganizationContactQuery(dbContext);
query = query.Where(e=>ContactName != null);
But this does not ...
internal static IQueryable<OrganizationContact> GetOrganizationContactQuery(dbContext ctx)
{
var query = from rContact in ctx.Contacts.Where(e => e.institution_id.HasValue)
join rPerson in ctx.People
on rContact.person_id equals rPerson.person_id into peopleGroup
from person in peopleGroup.DefaultIfEmpty()
select new BLL.OrganizationContact
{
ID = rContact.contact_association_id,
Title = rContact.contact_title,
Notes = rContact.notes,
CreatedBy = rContact.created_by,
CreatedDate = rContact.created_date,
UpdatedBy = rContact.updated_by,
UpdatedDate = rContact.updated_date,
ContactName = (person.name == null) ? null : person.name,
Person = new BLL.Person { ID = person.person_id, Name = person.name }
}
return query;
}
var query = GetOrganizationContactQuery(dbContext);
query = query.Where(e=>ContactName != null);
I have been through countless SO postings but none seem to have answered my question or identified what the underlying issue may be. Could be ignorance on my part though ...
