Trouble with where clause with modulo and substraction on entity field

Posts   
 
    
Rlaan avatar
Rlaan
User
Posts: 14
Joined: 21-Apr-2017
# Posted on: 31-May-2017 12:30:32   

Hello,

Using: LLBLGen version 5.1 (5.1.3) RTM with the LLBLGen Pro Runetime Framework. MSSSQL database version 2014, and the 4.5.2 .NET Framework, SelfServicing.

I'm trying to convert a query to llblgen but I have no success whatsoever, and was hoping someone could give me some guidance or help me out on where to read in the documentation since I can't find the right subject.

        SELECT AE.*
        FROM AgendaEvent AE
        RIGHT JOIN AgendaEventMeta AEM ON AEM.AgendaEventId = AE.Id
        WHERE  (( 1496188800 - RepeatStart) % RepeatInterval = 0 ) AND AEM.PatientId = 16;

Where 1496188800 is Helper.UnixTime.Today and 16 is patientId (parameter).

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 31-May-2017 19:39:45   

What exactly did you try? Are you using LLBLGen Low Level, Linq or QuerySpec?

Rlaan avatar
Rlaan
User
Posts: 14
Joined: 21-Apr-2017
# Posted on: 01-Jun-2017 10:02:35   

Walaa wrote:

What exactly did you try? Are you using LLBLGen Low Level, Linq or QuerySpec?

I am using Low Level throughout the project, and I tried it with Low Level and with QuerySpec for this query. Here are a few things I've been trying:

With Low Leve I initialy tried to use the PredicateExpression with the following line

var filter = new PredicateExpression();
filter.Add(AgendaEventMetaFields.PatientId == patientId);
filter.Add((Helper.UnixTime.Today - AgendaEventMetaFields.RepeatStart) % AgendaEventMetaFields.RepeatInterval);

But that's not allowed, then I tried digging some more into the documentation and found the ExOp enum hoping that could help me out, but couldn't find how to use it, so I did some trying out and found that the Expression class accepted them so I just wrote this:

var firstExpression = new Expression(Helper.UnixTime.Today, ExOp.Sub, AgendaEventMetaFields.RepeatStart);
var secondExpression = new Expression(firstExpression, ExOp.Mod, AgendaEventMetaFields.RepeatInterval);
 var thirdExpression = new Expression(secondExpression, ExOp.Equal, 0);

Then wondering if that is correct and if I can use it somewhere I did some more digging in the documentation and experimenting but I couldn't really figure it out. Then I thought maybe I can use it with QuerySpec and dynamic lists and at this point it was more a trial and error sort of thing so I can up with this:

 //var qf = new QueryFactory();
var q = qf.Create()
    .Select()
    .From(qf.AgendaEventMeta
        .RightJoin(qf.AgendaEvent)
            .On(AgendaEventMetaFields.AgendaEventId == AgendaEventFields.Id))
                 .Where((IPredicate)thirdExpression)
                 .AndWhere(AgendaEventMetaFields.PatientId == patientId);

Since I can't get the where clause that I went I just started atempting multiple trial and error cases like this, knowing it'll probably wont work.

Also looked for a way to maybe apply the expression, but I just don't know on how to tackle this in llblgen, and can't seem to find the right information in the documentation.

And currently I just made a store procedure from the query, call the stored procedure with llblgen, and convert the datatable to a AgendaEventCollection, which gets the job done but I'd rather use the llblgen low level api than a stored procedure.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Jun-2017 20:13:03   

You almost got it, till the second expression, and you don't need the third. Just use a PredicateExpression (FieldComapreValuePredicate) and use an EntityField for which you call the SetExpression method with the secondExpression of yours.

Something like:

var myField = AgendaEventMetaFields.ANY_FIELD.SetExpression(secondExpression);
filter.Add(myField == 0);
Rlaan avatar
Rlaan
User
Posts: 14
Joined: 21-Apr-2017
# Posted on: 02-Jun-2017 09:56:40   

Walaa wrote:

You almost got it, till the second expression, and you don't need the third. Just use a PredicateExpression (FieldComapreValuePredicate) and use an EntityField for which you call the SetExpression method with the secondExpression of yours.

Something like:

var myField = AgendaEventMetaFields.ANY_FIELD.SetExpression(secondExpression);
filter.Add(myField == 0);

Cheers simple_smile Got it working without the stored procedure now, thanks simple_smile Here's the method for others to see in-case they find themselves in the same situation. simple_smile


public static AgendaEventCollection GetAllEventsByDateAndPatientId(int unixTimeStamp, int patientId)
        {
            var subExpression = new Expression(unixTimeStamp, ExOp.Sub, AgendaEventMetaFields.EventUnixTimeStamp);
            var modExpression = new Expression(subExpression, ExOp.Mod, AgendaEventMetaFields.RepeatInterval);

            var filter = new PredicateExpression();
            filter.Add(AgendaEventMetaFields.Id.SetExpression(modExpression) == 0); 
            filter.AddWithAnd(AgendaEventMetaFields.PatientId == patientId);
            filter.AddWithOr(AgendaEventMetaFields.Id.SetExpression(subExpression) == 0);
            filter.AddWithAnd(AgendaEventMetaFields.PatientId == patientId);

            var relations = new RelationCollection();
            relations.Add(AgendaEventMetaEntity.Relations.AgendaEventEntityUsingAgendaEventId);

            var agendaEventCollection = new AgendaEventCollection();
            agendaEventCollection.GetMulti(filter, 0, null, relations);

            return agendaEventCollection;
        }