DevForce 2010: Busted entity query, might be too complex for LINQ

Print Page | Close Window

Busted entity query, might be too complex for LINQ

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2594
Printed Date: 29-Jul-2026 at 8:34pm

Topic: Busted entity query, might be too complex for LINQ

Posted By: wilmarvh
Subject: Busted entity query, might be too complex for LINQ
Date Posted: 30-Mar-2011 at 1:01am

Hi

I have run into strange problem. It might purely be a case of a lack in grey matter on my side, but here goes.

Background info:

I am building a search screen where users can search for 'Cases' based on criteria they enter. Not all criteria is required, they can enter what they want, pieces or whole words. I have a parent object called NOMSTask, which has all relevant properties. 'Case' and 'Work Item' both inherit from NOMSTask and then have their own extended fields as required. Pretty basic setup. We never use the NOMSTask on its own. It is always either a Case or a WorkItem. In the query below I am querying for Cases based on the search criteria entered.

I have tried to think of how to search for criteria only when it is entered (ie not null or empty), but I couldn't get anything to work. I tried PredicateDescriptions. But the biggest issue there is that I am selecting from Manager.Entities.NOMSTasks, but I need the objects to be of type Case.

Below are my 2 attempts:

This query's where statements don't work correctly. I guess it might be too complex for LINQ to properly translate into SQL. Each WHERE works seperately, and whole thing works if I don't use the inline if-statements, but then I have no null checking, and the query won't perform that good.



var query = from opsCase in Manager.Entities.NOMSTasks

                         where opsCase.TaskType.Description.ToLower() == "case"

                               && 
(string.IsNullOrEmpty(registrationNumber) ? true : 
opsCase.Asset.RegistrationNumber.Number.ToLower().Contains(registrationNumber))

                               && 
(string.IsNullOrEmpty(username) ? true : 
opsCase.Username.ToLower().Contains(username))

                               && (opsCase.Start > fromDate)

                               && (opsCase.End == null ? true : opsCase.End < toDate)

                         orderby opsCase.Asset.RegistrationNumber.Number ascending

                         select opsCase as Case;))))

And here is the predicate description route. Everything is fine but like I said, I don't know how to select the results as type Case instead of NOMSTask. If there is a Select() as Case parameter somewhere, I don't know where to find it.



            var predicateDescriptions = new List<PredicateDescription>();



            // search only for cases, not work items

            var type = PredicateBuilder.Make(typeof(Case), "TaskType.Description", FilterOperator.IsEqualTo, "case");

            type.IgnoreCase = true;



            // set the start/end date

            var start = PredicateBuilder.Make(typeof(Case), "Start", FilterOperator.IsGreaterThanOrEqualTo, fromDate);

            var end = PredicateBuilder.Make(typeof(Case), "End", FilterOperator.IsLessThanOrEqualTo, toDate);



            // add to our collection

            predicateDescriptions.Add(type);

            predicateDescriptions.Add(start);

            predicateDescriptions.Add(end);



            // check the registration number entered

            if (!string.IsNullOrEmpty(registrationNumber))

            {

                
predicateDescriptions.Add(PredicateBuilder.Make(typeof(Case), 
"Asset.RegistrationNumber.Number", FilterOperator.Contains, 
registrationNumber));

            }



            // check the username entered

            if (!string.IsNullOrEmpty(username))

            {

                predicateDescriptions.Add(PredicateBuilder.Make(typeof(Case), "Username", FilterOperator.Contains, username));

            }



            CompositePredicateDescription aCompositePredicateDescription
 = PredicateBuilder.And(predicateDescriptions.ToArray());

            var query = PredicateBuilder.FilterQuery(Manager.Entities.NOMSTasks, aCompositePredicateDescription);

This last line is where I can't seem to get past to select as Case and not NOMSTask.

Thanks in advance!

Wilmar

Replies:

Posted By: DenisK
Date Posted: 30-Mar-2011 at 11:00pm

Hi wilmarvh;

Have you tried using the .OfType extension method? This might work for your use case. Please see code sample below.

PreviousEmployee inherits from Employee.

PredicateDescription p1 = new PredicateDescription(typeof(Employee),

PreviousEmployee.PropertyMetadata.EmployeeID.Name, FilterOperator.IsGreaterThanOrEqualTo, 15);

PredicateDescription p2 = new PredicateDescription(typeof(Employee),

PreviousEmployee.PropertyMetadata.FirstName.Name, FilterOperator.StartsWith, "Test");

CompositePredicateDescription p3 = p1.And(p2);

var query = PredicateBuilder.FilterQuery(_em1.Employees, p3);

var r = query.OfType<PreviousEmployee>().ToList();

Posted By: wilmarvh
Date Posted: 30-Mar-2011 at 11:08pm

Thanks for the tip Denis, however I don't think I can use it.

The idea is to build up the required EntityQuery and then use it in our extended ObservableCollection class where we implemented paging and a few other things


PagingObservableCollection<Case>(query, pagingEnabled, pageSize, runQueryImmediately);

This collection runs async, and returns pages of results based on the parameters we send through. That is why I initially had the complex entity query which started all my headaches to begin with.

Posted By: DenisK
Date Posted: 31-Mar-2011 at 11:56am

Hi wilmarvh;

If I understand the requirement correctly, you should still be able to use that by simply casting the IQueryable to EntityQuery<T>. For example,

      IQueryable q1 = PredicateBuilder.FilterQuery(_em1.Employees, p3);
      EntityQuery<Employee> q2 = (EntityQuery<Employee>)q1;
      EntityQuery<PreviousEmployee> q3 = (EntityQuery<PreviousEmployee>)q1.OfType<PreviousEmployee>();

Print Page | Close Window