none
need help to do a reusable query function RRS feed

  • Question

  • I want to do this below:

    var r = from ...
     join jTrx in dc.GetTable<PJournalTransaction>()
     on cp.RecordID equals jTrx.BusinessEventId
    ...
     where ...             
     && MyHelpers.FilterP13(jTrx, p13Option)
    
        public bool FilterP13(PJournalTransaction journalTransaction, Period13Options p13Option)
        {
          switch (p13Option)
          {
            case Period13Options.Exclude:
              return !journalTransaction.Is13Period;
    
            case Period13Options.Only:
              return journalTransaction.Is13Period;
          }
    
          return true;
        }
    

    This JournalTransaction.Is13Period need to be filter in such way many places and times, so i looking for a reusable for that

    I got this "has no supported translation to SQL", more or less i knew the problem, its linq sql by design.

    i can't extend the entity object (for internal reason), i only can create this helper function outside of that entity.

    how can i do this?

    Wednesday, August 18, 2010 3:04 AM

Answers

  • You dont need && you can cascade wheres like this.

    where cp.DateOccurred >= fromDate

    where cp.DateOccurred <= toDate

    where Convert.ToBoolean(ReportHelper.JournalTransaction_FilterP13(p13Option))

    I think the problem is ReportHelper.JouralTransaction_FilterP13.  Is that a custom method?  Custom logics should not be embedded in query building logic  You should be using expressions.

    IQueryable<TableType> query = (from t in DataContext.TableType

    where cp.DateOccurred >= fromDate

    where cp.DateOccurred <= toDate

    select t);

    query.Where(GetMyExpression(option));  //From above

     

    You can always use application side querys.  Where you do you logic on lists instead of IQueryables. Which are slower but easier to do.

     

    Thursday, August 19, 2010 2:18 PM

All replies

  • You can't just write regular methods as they are compiled into IL which can't be sent to the SQL Server. What you need to do is make an Expression<Func<T,bool>> so that it stays as an expression and not compiled code.

    Secondly you need to make sure that the expression only contains code that is supported by SQL - switch is not one of those.  The following should work:

    Expression<Func<PJournalTransaction, Period13Options>> filterP13 = (j, p) => return p == Period13Options.Exclude ? ! j.Is13Period : (Period13Options.Only ? j.Is13Period : true);

    Now you can just do:

    where filterP13(jTrx, p13Option) at the end of your query.

    [)amien

    Wednesday, August 18, 2010 5:07 AM
    Moderator
  • I still get "has no supported translation to SQL" error.

          Expression<Func<PJournalTransaction, Period13Options?, bool>> fP13 = (journalTransaction, p13) => (p13 == null) ? journalTransaction.Is13Period : !journalTransaction.Is13Period;
          Func<PJournalTransaction, Period13Options?, bool> fP13c = fP13.Compile();
    
                 where ...
                 && fP13c(jTrx, p13Option)
    

    Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

    I have to use Compile, else code compile stop as treating the variable as method.

    Wednesday, August 18, 2010 7:22 AM
  • IQueryable<PJournalTransaction> query = DataContext.PJournalTransaction.AsQueryable();

    query.Where(YourExpression);

    query.ToList();

    Wednesday, August 18, 2010 2:45 PM
  • Secondly you need to make sure that the expression only contains code that is supported by SQL - switch is not one of those.  The following should work:

    [)amien


    I think this statement is misleading.  Damien is trying to say you cannot do this :

    //Does NOT Work
    public Expression<Func<PJournalTransaction, bool>> GetMyExpression(Period13Options option)
    {
     return (journal) =>
       {
        bool result;
        switch (option)
        {
         case Period13Options.Exclude :
           result = journal.Is13Period;
           break;
    
         case Period13Options.Include :
           result = !journal.Is13Period;
           break;
    
         default :
           result = true;
           break;
        }
        return result;
       };
    }
    
    

     

    I think you can do something like this if you wanted. The methods / properties inside the Expression statement matters IE the .Is13period has to be a column on a table. You could also do something like Convert.ToBoolean(journal.Is13Period) since Convert would be converted to Sql.

     

    public Expression<Func<PJournalTransaction, bool>> GetMyExpression(Period13Options option)
    {
     Expression<Func<PJournalTransaction, bool>> result;
     switch (option)
     {
      case Period13Options.Exclude :
        result = (journal) => journal.Is13Period;
        break;
    
      case Period13Options.Include :
        result = (journal) => !journal.Is13Period;
        break;
    
      default :
        result = (journal) => true;
        break;
     }
     return result;
    }
    
    Wednesday, August 18, 2010 6:48 PM
  • i got error

    Error 17 Operator '&&' cannot be applied to operands of type 'bool' and 'System.Linq.Expressions.Expression<System.Func<MYOB.Huxley.Presentation.Entities.PJournalTransaction,bool>>'

    when i do this

     

     

    where cp.DateOccurred >= fromDate && cp.DateOccurred <= toDate
    && ReportHelper.JournalTransaction_FilterP13(p13Option)

    ***

    when i tried this

                 where cp.DateOccurred >= fromDate && cp.DateOccurred <= toDate
                 && Convert.ToBoolean(ReportHelper.JournalTransaction_FilterP13(p13Option))
    
    

    i got this err at runtime

    {"Unable to cast object of type 'System.Linq.Expressions.Expression`1[System.Func`2[MYOB.Huxley.Presentation.Entities.PJournalTransaction,System.Boolean]]' to type 'System.IConvertible'."}

    ***

    i'm giving up the linq sql, it been a hassle in our work, and the architect don't even bother to look for cure.

    i'm ending it with

                 where cp.DateOccurred >= fromDate && cp.DateOccurred <= toDate
                 && ((p13Option == null) ? true :
                    (
                      (p13Option == Period13Options.Exclude) ? !jTrx.Is13Period :
                      (
                        (p13Option == Period13Options.Only) ? jTrx.Is13Period : true
                      )
                    )
                  )
    

    all over the places

    Thursday, August 19, 2010 2:52 AM
  • You dont need && you can cascade wheres like this.

    where cp.DateOccurred >= fromDate

    where cp.DateOccurred <= toDate

    where Convert.ToBoolean(ReportHelper.JournalTransaction_FilterP13(p13Option))

    I think the problem is ReportHelper.JouralTransaction_FilterP13.  Is that a custom method?  Custom logics should not be embedded in query building logic  You should be using expressions.

    IQueryable<TableType> query = (from t in DataContext.TableType

    where cp.DateOccurred >= fromDate

    where cp.DateOccurred <= toDate

    select t);

    query.Where(GetMyExpression(option));  //From above

     

    You can always use application side querys.  Where you do you logic on lists instead of IQueryables. Which are slower but easier to do.

     

    Thursday, August 19, 2010 2:18 PM
  • Kelman

    think of where your query is being executed???? its in a SQL engine yes???? does SQL understand your Enum??? 

    Ummm i think not, there if you where writing a SQL statement u would have to do something like

    where JT.IsPeriod13 = 1     -- to handle your enum when its Only

    where JT.IsPeriod13 = 0     -- to handle your enum when its Exclude

     

    to handle Include you dont even have to filter on anything.

    Second thing, i don't see your query being an architects problem it is just simple coding logic.

    Have a nice day. ;-)

     

     

     

     

    Wednesday, October 20, 2010 11:35 AM
  • Also if you need that logic everywhere, u can create a function that actually returns and IQueryable<PTransactionJournal>

    eg.

     

    internal static IQueryable<PTransactionJournal> GetFilteredTransactionJournal(DataContext dc, DateTime from, DateTime to, Period13Enum option)

    {

         var filter = dc.GetTable<PTransactionJournal>().Where(x=> x.Date >= from && x.Date <= to);

          if(option == Period13Enum.Only || option == Period13Enum.Exclude)

          {

                  var isOnlyPeriod13 = (option == Period13Enum.Only);

                  filter = filter.Where(x=> x.IsPeriod13 == isOnlyPeriod13);

          }

          return filter;

    }

     

     

     

    then in your millions of other queries u can do this:

     

    var result = from x in dc.GetTable<XXX>()

        join t in GetFilteredTransactionJournal(dc, DateTime.Now, DateTime.Now.AddYear(1), Period13Enum.Only) on x.SomeID equals t.SomeID 

    select what ever you want......

     

    Hope that helps.

     

    • Edited by CodeFarm Friday, October 22, 2010 11:29 AM typo
    Friday, October 22, 2010 11:27 AM