I'm having trouble with a linq query. I only want to restrict the query if the value isn't null.

In sql i'd write it like:

WHERE (@X IS NULL OR @X = Col1 OR @X = Col2)

I tried this:

var queryResults = from c in meta.CustomerQuotation
                               join o in
                                   (from c in meta.CustomerQuotation
                                    where c.QuoteNo == query || c.Surname == query || query == null
                                    group c by c.QuoteNo into g
                                    select new { QuoteNo = g.Key, Version = g.Max(p => p.Version) }) on new { c.QuoteNo, c.Version } equals new { o.QuoteNo, o.Version }
                               select c;

See the query == null bit (query is variable of type string)

But i get an exception:

The binary expression '(False = True)' can't be converted to a predicate expression.

So how should I do this?

Have you tried the predicate builder? See ref.

The thing here I think is that the "query == null" is treated as an in-memory expression, not really a query where clause, that's why the expression is evaluated (resulting in True or False). You also can't use a functionMapping because you are not filtering or applying some function to a field.

What about:

if (string.IsNullOrEmpty(query))
{
    // restrict
    var q = ....
}
else
{ 
     // don't restrict
}

? Isn't that much easier?

The predicate builder is what allows for this complex where clause in linq right?

this is in the restrictions section: a boolean expression with a constant value isn't supported, as it's not convertable to a predicate in llblgen pro.

Easy workaround though:

if(query!= null)
{
       var queryResults = from c in meta.CustomerQuotation
                             join o in
                                 (from c in meta.CustomerQuotation
                                    where c.QuoteNo == query || c.Surname == query
                                    group c by c.QuoteNo into g
                                    select new { QuoteNo = g.Key, Version = g.Max(p => p.Version) }) on new { c.QuoteNo, c.Version } equals new { o.QuoteNo, o.Version }
                             select c;
}

No guys writing two seperate queries depending on whether 'query' is null or not is not easier, it's messy. If i have n number of search critieria I should write n number of queries * all the possible combinations?!

If i were using RelationPredicateBucket i'd be writing something like

inst.Add(TheEntityFields.Surname == DBNull.Value | TheEntityFields.Surname == mySurnameVariable);

I think that's right... i did it a few weeks back but can't find where.

I spose I'll take a look at this predicate builder... wasn't linq supposed to make things easier?

Linq does not (nor will it ever) change the fact that sometimes business logic is messy. There is no magic bullet that will materialize business rules. If there was, we wouldn't have a job. The truth of the matter is that a variable is being passed in that has to be checked for nullability. The Linq expression forms a closure over that variable and attempts to convert it to SQL which is NOT possible. Notice that query (since it is a reference variable) has a value greater than 1 (or true) and null does not (false). The expression gets evaluated that way. There is no way around it.

What your query would return would be all the entities with a null surname + all the entities that have the surname of the closed over variable.

Next, I believe I can create a reduction from Circuit SAT to your problem as you have set it up, since you have a number of variables and'ed and or'ed together and you want to find satisfiability. So if you do find a magic algorithm that solves your problem, then you have solved a problem that has eluded computer scientists for 50 years.

Finally, using the Predicate builder I was able to create the where clause to the linq expression just fine using the nullability checks. Here is the example:

public Expression<Func<VehicleEntity, bool>> RetrievePredicateExpression()
{
     Expression<Func<VehicleEntity, bool>> predicate = PredicateBuilder.Null<VehicleEntity>();

     if(!checkAllLocations.Checked)
          predicate.And<VehicleEntity>(v => this.SelectedLocations.Contains(v.AssignedTo));


    if (checkAssigned.Checked)
        predicate = predicate.And<VehicleEntity>(v => v.EmployeeAssigned != null);
    else
        predicate = predicate.And<VehicleEntity>(v => v.EmployeeAssigned == null);

    this.SelectedFuelTypes = _fuelTypeControl.GetCheckedItems();
    if (this.SelectedFuelTypes != null && this.SelectedFuelTypes.Count > 0)
        predicate = predicate.And<VehicleEntity>(v => this.SelectedFuelTypes.Contains(v.FuelTypeId));
    
    this.SelectedLessors = _lessorControl.GetCheckedItems();
    if (this.SelectedLessors != null && this.SelectedLessors.Count > 0)
        predicate = predicate.And<VehicleEntity>(v => this.SelectedLessors.Contains(v.LessorId));

    this.SelectedMakes = _makeControl.GetCheckedItems();
    if (this.SelectedMakes != null && this.SelectedMakes.Count > 0)
        predicate = predicate.And<VehicleEntity>(v => this.SelectedMakes.Contains(v.MakeId));

    this.SelectedVehicleStatus = _vehicleStatusControl.GetCheckedItems();
    if (this.SelectedVehicleStatus != null && this.SelectedVehicleStatus.Count > 0)
        predicate = predicate.And<VehicleEntity>(v => this.SelectedVehicleStatus.Contains(v.VehicleStatusId));

    this.SelectedVehicleTypes = _vehicleTypeControl.GetCheckedItems();
    if (this.SelectedVehicleTypes != null && this.SelectedVehicleTypes.Count > 0)
        predicate = predicate.And<VehicleEntity>(v => this.SelectedVehicleTypes.Contains(v.VehicleTypeId));

    return predicate;
}

I then pass that predicate expression to a function that does this:

public IList<VehicleViewData> GetVehiclesByExpression(Expression<Func<VehicleEntity, bool>> whereClause)
{
    using (IDataAccessAdapter adapter = this._adapter.Create())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        return ToVehicleView(
            (metaData.Vehicle
                    .Where<VehicleEntity>(whereClause)
                    .WithPath<VehicleEntity>(v => v.Prefetch(e => e.Employee)
                        .Prefetch(m => m.Make)
                        .Prefetch(m => m.Model)
                        .Prefetch(s => s.VehicleStatus)
                        .Prefetch(l => l.Lessor)
                        .Prefetch(a => a.AssignedToLocation)
                        .Prefetch(o => o.OwnedByLocation)) as ILLBLGenProQuery)
            .Execute<EntityCollection<VehicleEntity>>());
    }
}

There is other stuff going on but I think you get the picture.

Oh, and no, I don't work for LLBLGen, I've just used their product since the stored procedure version and find it to be quite excellent.

Ok... it's just conditional condition. Why is that such a big ask?? I guess this is more of a LINQ problem in general rather than anything specific to LLBL's capabilities.

Im having some success with the ternary operator. This is from a different example but appears to work:

var data = from c in meta.Game
                           join gc in meta.GameCategory on c.GameId equals gc.GameId
                           where categories.Length > 0 ? categories.Contains(gc.CategoryId) && c.Name.Contains(name) : c.Name.Contains(name)
                           orderby c.Name
                           select c;

categories is an int array.

It's far from ideal and I can see I'll hit some walls if it gets any more complicated... but least it gets me though that.

Anyway thanks for code Seth

The main problem with your initial setup is that it has to produce a predicate which emits True=true (or 1=1), and the framework doesn't have such a predicate.

As these kind of predicates always could be eliminated by the developer, we didn't write code for this (as linq in general already took long enough). It's not that this is undeterministic: it's always deterministic what predicate will result in a constant comparison, as both sides of the logical operator are constants.

Looking back I can tell I was a bit rude! Sorry! I think the thing that worked for me was focusing on the where clause and passing that expression into the function. It was nicer in the sense that I let something else build the where expression and handled the actual data pull in the service layer. Once again, sorry for the rudeness.

Hi Frans,

Otis wrote:

The main problem with your initial setup is that it has to produce a predicate which emits True=true (or 1=1), and the framework doesn't have such a predicate.

But it doesn't need to emit a predicate like 1=1, ok my attempted approach would mean it would but what's needed is a some syntax to evaluate an expression in the runtime that determines if a predicate should be included in the first place (like what happens when I use the ternary operator). But as I said that's probably not your job and is a limitation of the linq syntax.

worldspawn wrote:

Hi Frans,

Otis wrote:

The main problem with your initial setup is that it has to produce a predicate which emits True=true (or 1=1), and the framework doesn't have such a predicate.

But it doesn't need to emit a predicate like 1=1, ok my attempted approach would mean it would but what's needed is a some syntax to evaluate an expression in the runtime that determines if a predicate should be included in the first place (like what happens when I use the ternary operator). But as I said that's probably not your job and is a limitation of the linq syntax.

Ternary operator usage results in a case statement, so that's covered. The main thing is that the Linq provider has to build predicateexpressions and it runs into a predicate which is a constant. Ignoring is not an option, as it can only do that by interpreting the whole predicate but that's not possible as it needs data to do that (so that can be done in the DB only). So it has to emit a constant predicate, which isn't possible for our framework.

If we had a predicate which simply would emit 1=1, or 1=0, it wouldn't be a problem anymore, as any boolean operation in your query would be handleable, even if it results in a constant.

It's not possible to run into this situation if you have a boolean operation which refers to an element in the DB, it's only possible if you have a boolean operation which refers to in-memory elements and/or constants. This is always refactorable to an if statement. At least, that was the reasoning behind not adding the predicate.