none
Call method in where clause RRS feed

  • Question

  • How I can call method in where clause?

    I have that:

    var result = from pi in db.ProcessoImportacaoDetalhe
                 where pi.DataNotaFiscal >= dtNFInicial
                 select pi;

    But, I have a problem because the field DataNotaFIscal contains a long date (dd/MM/yyyy hh:mm:ss.fff) and my filter field contains a short date (dd/MM/yyyy).

    How I compare the dates considering only Short format?

    I tryed this:

    var result = from pi in db.ProcessoImportacaoDetalhe
                 where GetDate(pi.DataNotaFiscal) >= dtNFInicial
                 select pi;
    
    public static DateTime? GetDate(DateTime? date)
    {
       DateTime? dt = null;
    
       if (date != null)
           dt = new DateTime(date.Value.Year, date.Value.Month, date.Value.Day);
    
       return dt;
    }

    But I receive this message:

    LINQ to Entities does not recognize the method 'System.Nullable`1[System.DateTime] GetDate(System.Nullable`1[System.DateTime])' method, and this method cannot be translated into a store expression.

    Somebody has any idea for help me?


    Deise Vicentin
    "Eu não procuro saber as respostas, procuro compreender as perguntas." Confúcio

    Monday, May 14, 2012 6:40 PM

Answers

  • I would propose an alternative implementation - since you know your date for the filter is just the date, I would pre-create that date + one day later, and search within a range:

    // Make sure this is just the Date, and not including a time
    dtNFInicial = dtNFInicial.Date;
    DateTime dtNFInicialPlusOne = dtNFInicial.Date.AddDays(1);
    
    var result = from pi in db.ProcessoImportacaoDetalhe
                 where pi.DataNotaFiscal >= dtNFInicial 
                    && pi.DataNotaFiscal < dtNFInicialPlusOne
                 select pi;

    This will keep all of the processing on the server, and work correctly.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Hi Reed;

    Using your idea, I did this:

    public List<T> GetAll(Filter filter) { DateTime? dtNFInicial = filter.dtNFInicialDT(); DateTime? dtNFFinal = filter.dtNFFinalDT(); var result = from pi in db.ProcessoImportacaoDetalhe where pi.DataNotaFiscal >= dtNFInicial && pi.DataNotaFiscal <= dtNFFinal select pi;

    return result.ToList(); } public class Filter { public DateTime? dtNotaFiscalFinalDT() { if (dtNotaFiscalFinal != "") return Convert.ToDateTime(dtNotaFiscalFinal + " 23:59:59.999"); return null; } }

    So, where my register has date equals 2011-09-22 10:49:17.000 and the filters (dtNotaFiscalInicial and dtNotaFiscalFinal) are 2011-09-22, the registers are returned ignoring the hours, minutes and seconds.


    Deise Vicentin
    "Eu não procuro saber as respostas, procuro compreender as perguntas." Confúcio


    Monday, May 14, 2012 7:31 PM

All replies

  • I would propose an alternative implementation - since you know your date for the filter is just the date, I would pre-create that date + one day later, and search within a range:

    // Make sure this is just the Date, and not including a time
    dtNFInicial = dtNFInicial.Date;
    DateTime dtNFInicialPlusOne = dtNFInicial.Date.AddDays(1);
    
    var result = from pi in db.ProcessoImportacaoDetalhe
                 where pi.DataNotaFiscal >= dtNFInicial 
                    && pi.DataNotaFiscal < dtNFInicialPlusOne
                 select pi;

    This will keep all of the processing on the server, and work correctly.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, May 14, 2012 7:07 PM
  • I would propose an alternative implementation - since you know your date for the filter is just the date, I would pre-create that date + one day later, and search within a range:

    // Make sure this is just the Date, and not including a time
    dtNFInicial = dtNFInicial.Date;
    DateTime dtNFInicialPlusOne = dtNFInicial.Date.AddDays(1);
    
    var result = from pi in db.ProcessoImportacaoDetalhe
                 where pi.DataNotaFiscal >= dtNFInicial 
                    && pi.DataNotaFiscal < dtNFInicialPlusOne
                 select pi;

    This will keep all of the processing on the server, and work correctly.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Hi Reed;

    Using your idea, I did this:

    public List<T> GetAll(Filter filter) { DateTime? dtNFInicial = filter.dtNFInicialDT(); DateTime? dtNFFinal = filter.dtNFFinalDT(); var result = from pi in db.ProcessoImportacaoDetalhe where pi.DataNotaFiscal >= dtNFInicial && pi.DataNotaFiscal <= dtNFFinal select pi;

    return result.ToList(); } public class Filter { public DateTime? dtNotaFiscalFinalDT() { if (dtNotaFiscalFinal != "") return Convert.ToDateTime(dtNotaFiscalFinal + " 23:59:59.999"); return null; } }

    So, where my register has date equals 2011-09-22 10:49:17.000 and the filters (dtNotaFiscalInicial and dtNotaFiscalFinal) are 2011-09-22, the registers are returned ignoring the hours, minutes and seconds.


    Deise Vicentin
    "Eu não procuro saber as respostas, procuro compreender as perguntas." Confúcio


    Monday, May 14, 2012 7:31 PM