I'm using LLBLGen Pro 5.4 with Npgsql 3.2.7. I've come across what I believe is a bug with PostgreSQL date arithmetic. It seems like it's almost identical to this issue posted a few years ago:

https://llblgen.com/tinyforum/Messages.aspx?ThreadID=22961

This C# code...

            var memberAuditReadiness = new MemberAuditReadinessInfo
            {
                IsActive = LLBLGenUtils.db.User
                    .Any(u => u.MemberId == memberId && u.LastLoginDateUtc.HasValue && u.LastLoginDateUtc.Value.AddMonths(MemberAuditReadinessConsts.MonsSinceLastLoginForActiveMembers) > DateTime.UtcNow)
            };

...is being turned into this query...

SELECT CASE WHEN EXISTS (SELECT "LPLA_1"."usetwofactorauthentication" AS "UseTwoFactorAuthentication" FROM "public"."User" "LPLA_1" WHERE ( ( ( ( "LPLA_1"."memberid" = $1) AND ( "LPLA_1"."lastlogindateutc" IS NOT NULL)) AND ( CAST(("LPLA_1"."lastlogindateutc" + interval ':p5 month') AS date) > $2)))) THEN 1 ELSE 0 END AS "LPFA_2" FROM "public"."User" "LPLA_1" LIMIT 1

...which is resulting in this error:

invalid input syntax for type interval: ":p5 month" at character 273

Hmm, the thread you link to is an Oracle thread, but apparently the behavior is similar in postgresql. We'll look into it.

FYI, to temporarily get around this specific issue, I added the following FunctionMappingStore to my LinqMetaData object:

FunctionMappingStore funcMap = new FunctionMappingStore();
funcMap.Add(new FunctionMapping(typeof(DateTime), "AddMonths", 1, "({0} + ({1} || ' months')::interval)"));

Thanks, it's indeed the same problem as with oracle: the operators as listed here (https://www.postgresql.org/docs/9.0/static/functions-datetime.html) work only on literals, not parameters.

We'll rework the mappings so they'll work with parameters. You can keep using the workaround, an update won't be affected by it. It's surprisingly hard to find real info about this tho. Almost all docs specify hard-coded arguments for the # of days/months etc. to add...

(edit) make_interval(_type_ := param) seems to be the official one (npgsql uses it too for EF it seems) but concatenating it with ::interval looks like to be the same thing (although slower).

Sad thing is make_interval was introduced in 9.4 and 9.3 is still in support, so we'll go with the slower concat route...

Implemented using _functionMappings.Add(new FunctionMapping(typeof(DateTime), "AddDays", 1, "({0} + ({1} * '1 day'::interval))"));

which doesn't use a string concatenation. ({0} + make_interval(days := {1}) should be faster still but not supported on 9.3 so we won't use that.

in next hotfix build (later today)

It's now available