filtering list using linq

Question

I am writing to seek help, in how can I filter data and then merge two results, below:

Update code*

public class TestController : ApiController
{
    private cdw db = new cdw();

    public HttpResponseMessage get([FromUri] Query query)
    {
        var data = db.data_qy.AsQueryable();

        if (query.startDate != null)
        {
            data = data.Where(c => c.UploadDate >= query.startDate);
        }

        if (!string.IsNullOrEmpty(query.tag))
        {
            var ids = query.tag.Split(',');

            data = data.Where(c => c.TAG.Any(t => ids.Contains(t)));
        }

        if (!string.IsNullOrEmpty(query.name))
        {
            var ids = query.name.Split(',');

            data = data.Where(c => c.NAME.Any(t => ids.Contains(t)));
        }

        if (!data.Any())
        {
            var message = string.Format("No data found");
            return Request.CreateErrorResponse(HttpStatusCode.NotFound, message);

        }

        return Request.CreateResponse(HttpStatusCode.OK, data);
    }
}

entity class:

  public partial class data_qy
   {
    public int ID { get; set; }


    public string Name { get; set; }

    public string TAG { get; set; }

    public string TAG_IS { get; set; }

    [Newtonsoft.Json.JsonProperty(PropertyName = "Date")]
    public Nullable<System.DateTime> UploadDate { get; set; }

}

Sample Dataset:

 Name               Tag          Tag_IS
 AMCAR 2013-5   03065EAC9   
 ARES 2006-6RA  04009JAA9   
 ARES 2012-1A   04013TAB9   
 ATOM 2003-I A           0182690668
 BACM 2006-2 AM 05950EAG3   
 BCAP 2007-AA3  05530VAN9   
 BCAP 2007-AA3  05530VAN9   
 BCJAF 9 C                   0312888037
 BLNDLN 0                    0213093627
 BLNDLN 0                    0213093627

The underlying SQL query should resemble:

select * 
from [dbo].[data_qy] 
where TAG like '%78473TAC4%' 
    or TAG LIKE '%05946XYZ0%' 
    OR NAME LIKE '%TAP%' 
    OR NAME LIKE '%STORM%'

Using the following list method above, when I run a query such as (api/test/tag=78473,12669,05946,... (30 values)), i get a -- Exception Message:

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

Am I missing something. Any help would be most appreciated. Thanks

Answer 1

I'm not in a situation to test this on the spot, but I suspect that either of the following should work:

public class TestController : ApiController
{
    private cdw db = new cdw();

    public HttpResponseMessage get([FromUri] Query query)
    {
        IQueryable<data_qy> data = null;

        if (!string.IsNullOrEmpty(query.tag))
        {
            var ids = query.tag.Split(',');

            var dataMatchingTags = db.data_qy.Where(c => ids.Any(id =>  c.TAGS.Contains(id)));

            if (data == null)
                 data = dataMatchingTags;
            else
                 data  = data.Union(dataMatchingTags);
        }

        if (!string.IsNullOrEmpty(query.name))
        {
            var ids = query.name.Split(',');

            var dataMatchingName = db.data_qy.Where(c => ids.Any(id =>  c.NAME.Contains(id)));

            if (data == null)
                 data = dataMatchingName;
            else
                 data  = data.Union(dataMatchingName);
        }

        if (data == null) // If no tags or name is being queried, apply filters to the whole set of products
            data = db.data_qy;

        if (query.startDate != null)
        {
            data  = data.Where(c => c.UploadDate >= query.startDate);
        }

        var materializedData = data.ToList();

        if (!materializedData.Any())
        {
            var message = string.Format("No data found");
            return Request.CreateErrorResponse(HttpStatusCode.NotFound, message);

        }

        return Request.CreateResponse(HttpStatusCode.OK, materializedData);
    }
}

I also suspect that you don't need to check against Null in your query, since EF will understand that when transforming the Expression Tree to SQL, but if needed, you can add it.

That would remove the need to use the foreach, the Aggregate and the call to Count. and results in a much simpler query that should use the IN operator in SQL.

Currently you're executing the same query multiple times (the call to .Any at the end will execute the query and then passing the data variable will execute it again. This can be very costly. Instead, materialize the results and act upon that, as above.