I looked through the documentation and the forums, but I can't find any information on this. I’m curious if there is any way to achieve something like the following with LINQ for LLBLGen.

public int CountByDateRange(DateTime start, DateTime end) {
    using (var adapter = new DataAccessAdapter()) {
        var filter = new FieldBetweenPredicate(
            OrderFields.DateOrdered, null, start, end);
        return adapter.GetDbCount(
            new EntityFields2(1) { OrderFields.Id },
            new RelationPredicateBucket(filter));
    }
}

I am using LLBLGen v2.6 Adapter, VS 2008, .NET 3.5

THere's no between equivalent, as the Linq syntax doesn't offer that. So instead use two compares and an '&&' / And statement.

Otis wrote:

THere's no between equivalent, as the Linq syntax doesn't offer that. So instead use two compares and an '&&' / And statement.

I tried that but the resultant SQL ends up being something like:

SELECT ...
WHERE ((Field1 > @Param1) AND (Field1 < @Param2))

I tried every combination of >, <, >=, <=. I wouldn't expect a translation to BETWEEN, however BETWEEN is useful because the upper and lower bounds don't have to be in sequential order.

It would be nice if LLBLGen would implement an extension method like:

public static bool Between<T>(this T value, T a, T b) where T : IComparable<T> {
    if (a.CompareTo(b) <= 0)
        return value.CompareTo(a) >= 0 && value.CompareTo(b) <= 0;
    else
        return value.CompareTo(b) >= 0 && value.CompareTo(a) <= 0;
}

but of course using real types. And then when parsing the LINQ expressions, map the Between function call to the database representation.

Then you could do something like:

**from **o **in **meta.Order **where **o.DateOrdered.Between(start, end) **select **o;

I'm not sure if it would work, just an idea.

Otis wrote: THere's no between equivalent, as the Linq syntax doesn't offer that. So instead use two compares and an '&&' / And statement.

I tried that but the resultant SQL ends up being something like:

Code:

SELECT ... WHERE ((Field1 > @Param1) AND (Field1 < @Param2))

Reading Frans' suggestion, this is the expected output query. So nothing wrong with it.

A between is a compare with a >= and a < or a > and a <=. You don't have an equal comparison in your two comparisons, which means you miss a value which Between would have picked up.

Your suggestion can't be done. The 'Between' extension method has to be defined on all types a field can be, e.g. string, int etc. so also on types which can be provided with type converters. The reason is that customer.Country for example is of type 'string', it's not of type EntityField. So if you want to write: where o.OrderDate.Between(dateA, dateB) the .Between extension method isn't defined on an entity field, but on a property of type DateTime, and thus has to be an extension method which is an extension method of DateTime.

If you want, you can define these though. You can map a method you write yourself onto a database construct through function mappings (see this manual page). So you could for example create a method which is usable as: ... where MyMethods.Between(o.OrderDate, dateA, dateB) select o;

though I'd simply write the two compares and be done with it