CODEWITHSUNDEEP
entity-frameworklinqlinq-to-sqllinq-to-entities
Samirovic
Samirovic

Reputation: 74

Select many columns error entity framework (Unable to create a null constant value of type)

I don't want all columns to be returned so I'm trying to select some columns from EMPLOYEES, I got an error when running the following code:

    var query = db.EMPLOYEES.AsQueryable();
query = ApplyFilter(query, filter);

var result = query.Select(x => new EMPLOYEE_DTO()
{
     PHONE_NO = x.PHONE_NO,
     EMAIL = x.EMAIL,
     EMP_NO = x.EMP_NO
}).ToList();

private IQueryable<EMPLOYEE> ApplyFilter(IQueryable<EMPLOYEE> query, EmployeesFilter filter)
{
   if (!string.IsNullOrEmpty(filter.EMAIL))
       query = query.Where(u => u.EMAIL.ToLower().Contains(filter.EMAIL.ToLower()));
    return query;
}

The error is: {"Unable to create a null constant value of type 'System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only entity types, enumeration types or primitive types are supported in this context."}

I saw a post saying that using DTO object (EMPLOYEE_DTO) will solve the issue but the issue still exist.

Please advice.

Upvotes: 0

Views: 105

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

You don't tell us a lot about your classes. I assume that filter is an EmployeesFilter object in your local process; db is an object of type DbContext; and db.Employees is an object of DbSet<Employee>.

Both filter and Employee have a string property Email

Every DbSet<Employee> implements IQueryable<Employee>. So why are you calling AsQueryable?

Your filter is a local variable. In this context only filter.Email is important. If the value of this property equals null, you want all Employees, otherwise you want a subset of the Employees, namely only those Employees that have an Email value equal to the Email value in filter, ignoring Case.

Now ignoring case is always dangerous. After all, not everyone has names with only letters A-Z and a-z. It is better to describe what case ignorance you want: ordinal? current culture? invariant culture?

It seems you want the following:

var query = String.IsNullOrEmpty(filter.Email) ?
    db.Employees :
    db.Employees            
      .Where(employee => String.Equals(employee.Email, filter.Email, 
           StringComparison.InvariantCultureIgnoreCase);

I'm not sure if changing this into an extra function like ApplyFilter would improve readability, but if you really want, write it as an extension function for IQueryable<Employee>:

static IQueryable<Employee> ApplyFilter(this IQueryable<Employee> employees, 
       EmployeeFilter filter)
{
    return String.IsNullOrEmpty(filter.Email) ? 
           employees :
           employees.Where(employee => String.Equals(employee.Email, filter.Email, 
               StringComparison.InvariantCultureIgnoreCase);
}

Usage for any query:

IQueryable<Employee> someEmployees = ...
IQueryable<Employee> filteredEmployees = someEmployees.Filter(myFilter);

I deliberately didn't use var, so you could see what happens to the types.

TODO: check if this code is correct by adding ToList() to see if the Employees can be fetched. But of course you already did that, didn't you?

Now your second part. So you have a query that returns an IQueryable<Employee> and you want to project every Employee into an EmployeeDto. If you want to do this in your query, you'll have to make sure that your EmployeeDto is a POCO: a class with a default constructor and only get / set properties.

If that is the case you should be able to perform your projection:

var result = query.Select(employee => new EMPLOYEE_DTO()
{
     PHONE_NO = employee.PHONE_NO,
     EMAIL = employee.EMAIL,
     EMP_NO = employee.EMP_NO,
})
.ToList();

Upvotes: 0

Related Questions