CODEWITHSUNDEEP
c#entity-frameworklinq
Jude
Jude

Reputation: 2433

Linq to Entities - where statement throws System.NotSupported Exception

   var entity =
   from document in db.Context.DocumentEntity
   join product in db.Context.ProductEntity on document.ProductId equals product.Id
   join partner in db.Context.PartnerEntity on product.PartnerId equals partner.Id
   select new
   {
       document,
       product,
       partner
   } into t1
   where request.PartnerFilter.Contains(t1.partner.Name)
   group t1 by t1.document.Date into rp
   select new
   {
       PartnerName = rp.FirstOrDefault().partner.Name,
       Date = rp.FirstOrDefault().document.Date,
       Income = rp.Sum(x => x.document.Income),
       Click= rp.Sum(x => x.document.Click)
   };

result = ToDataTable(entity.OrderByDescending(d=>d.Date).ToList());


public static DataTable ToDataTable<T>(List<T> items)
{
  DataTable dataTable = new DataTable(typeof(T).Name);

  PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
  foreach (PropertyInfo prop in Props)
  {
    var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
    dataTable.Columns.Add(prop.Name, type);
  }
  foreach (T item in items)
  {
    var values = new object[Props.Length];
    for (int i = 0; i < Props.Length; i++)
    {
        values[i] = Props[i].GetValue(item, null);
    }
    dataTable.Rows.Add(values);
  }
 return dataTable;

}

The problem is on where clause. request.PartnerFilter is a string array and might be null. I need to check if partner.Name is included in it. Kind of Sql Where-In. In the end entity.ToList() throws System.NotSupported Exception. How can I accomplish to filter?

Upvotes: 1

Views: 365

Answers (3)

The One
The One

Reputation: 4784

Take this request part out of the equation because Entity Framework doesn't know what to do with a Request object, it can handle strings, string arrays, etc.

string[] strArray=request.PartnerFilter;

var entity =
from document in db.Context.DocumentEntity
join product in db.Context.ProductEntity on document.ProductId equals product.Id
join partner in db.Context.PartnerEntity on product.PartnerId equals partner.Id
select new
{
   document,
   product,
   partner
} into t1
//Check if null
where strArray!=null && strArray.Any() && strArray.Contains(t1.partner.Name)
group t1 by t1.document.Date into rp
select new
{
   PartnerName = rp.FirstOrDefault().partner.Name,
   Date = rp.FirstOrDefault().document.Date,
   Income = rp.Sum(x => x.document.Income),
   Click= rp.Sum(x => x.document.Click)
};

Also, use Navigation Properties instead of joins

Upvotes: 2

Ivan Stoev
Ivan Stoev

Reputation: 205609

If you want to use Contains inside the EF query expression tree, you need to ensure the variable is not null. And you need to do that (along with the condition if it needs to be applied) outside the query.

For instance:

var partnerFilter = request.PartnerFilter ?? Enumerable.Empty<string>();
bool applyPartnerFilter = partnerFilter.Any();

var entity =
    ...
    where (!applyPartnerFilter || partnerFilter.Contains(t1.partner.Name))
    ...

But in my opinion it would be much better to apply the optional filter(s) outside the query, for instance:

var partners = db.Context.PartnerEntity.AsQueryable();
if (request.PartnerFilter != null && request.PartnerFilter.Any())
    partners = partners.Where(partner => request.PartnerFilter.Contains(partner.Name));

var entity =
...
join partner in partners on product.PartnerId equals partner.Id
...

(no where)

Upvotes: 4

cSteusloff
cSteusloff

Reputation: 2628

You use the SQL WHERE IN () Clause with Contains correct. Your only problem is the possible null exception.

What should happen if the array is empty? Would you like to have all the values? Use true if array is null otherwise false

Try this:

string[] partnerNames = request.PartnerFilter;

var entity =
   from document in db.Context.DocumentEntity
   join product in db.Context.ProductEntity on document.ProductId equals product.Id
   join partner in db.Context.PartnerEntity on product.PartnerId equals partner.Id
   select new
   {
       document,
       product,
       partner
   } into t1
   where partnerNames?.Contains(t1.partner.Name) ?? true
   group t1 by t1.document.Date into rp
   select new
   {
       PartnerName = rp.FirstOrDefault().partner.Name,
       Date = rp.FirstOrDefault().document.Date,
       Income = rp.Sum(x => x.document.Income),
       Click= rp.Sum(x => x.document.Click)
   };

WHERE IN - as query Syntax

var selected = from document in Document 
               where new[] {"Paul", "Peter"}.Contains(document.UserName)
               select document

WHERE IN - as method Syntax

var selected = Document
               .Where(d => new[] ["Paul","Peter"}.Contains(d.UserName))

Upvotes: 1

Related Questions