locked
Create a filter with function in where clause RRS feed

Answers

  • User-893317190 posted

    Hi neoaguil17,

    As PatriceSc  has said, your  linq will be  translated to sql and  it couldn't  convert tostring("dd/MM/yyyy") to sql, you should directly use sql function to convert datetime to string.

    Below is my code.

      var strs = dbContext1.Entity1.Where(en => (en.mydate.Value == null ? "" : 
    SqlFunctions.DatePart("dd", en.mydate) + "/" // get the day using sqlfunction
    + SqlFunctions.DatePart("MM", en.mydate) + "/" // get the month using sqlfunction
    + SqlFunctions.DatePart("yyyy", en.mydate)) // get the year using sqlfunction
    .Contains("2018") // then you could use contains

    );

    var abc = strs.Select(en => new { time = en.mydate.Value == null ? "" : SqlFunctions.DatePart("dd", en.mydate) + "/" + SqlFunctions.DatePart("MM", en.mydate) + "/" + SqlFunctions.DatePart("yyyy", en.mydate) }).ToList(); GridView1.DataSource = abc; GridView1.DataBind();

    The result.

    But you should pay attention that   SqlFunctions.DatePart returns int?, if you want a string  09 for september  you should add  0 before it.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 14, 2019 2:43 AM

All replies

  • User-1516073966 posted

    Hi,

    Refer the link below.

    https://stackoverflow.com/questions/17187395/using-my-own-method-with-linq-to-entities

    Thanks,

    Chandra Shekar Y

    Friday, January 11, 2019 7:50 AM
  • User-893317190 posted

    Hi neoaguil17,

    From your exception , it seems that you are using custom methods in your linq.

    Please check where you use custom method linq, I couldn't see where you use your custom method.

     If you want to use custom method in linq, please refer to the linq below.

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/how-to-add-custom-methods-for-linq-queries

    If you still have a problem, please post the code where you use the custom method and tell us what you want to achieve.

    Best regards,

    Ackerly Xu

    Friday, January 11, 2019 9:14 AM
  • User753101303 posted

    Hi,

    Also your C# code is translated to SQL so you needs to use things that Linq knows how to translate. I'm not sure to get what you are trying to do make sure this is your best option (you really try to find if characters are found in a date ?). It seems to me it would be best to have specialized filtering so that users can easily filter between two dates rather than dates having "20" in them (if this is what you are trying to do).

    Friday, January 11, 2019 9:22 AM
  • User-1350042179 posted

    Hi

    This is the complete linq,

     var proyectos = (from p in context.Proyecto
                    join rp in context.RecursoProyecto on new { IdProyecto = p.IdProyecto, IdRol = 27 }
                    equals new { IdProyecto = rp.IdProyecto.Value, IdRol = rp.IdRol.Value } into Proyecto_join
                    from rp in Proyecto_join.DefaultIfEmpty()
                    join r in context.Recurso on rp.IdRecurso equals r.IdRecurso into Recurso_join
                    from r in Recurso_join.DefaultIfEmpty()
                    join c in context.Cliente on p.IdCliente equals c.IdCliente into Cliente_join
                    from c in Cliente_join.DefaultIfEmpty()
                    join est in context.EstadosProyecto on p.IdEstadoProyecto equals est.IdEstadoProyecto into EstadoProyecto_join
                    from est in EstadoProyecto_join.DefaultIfEmpty()
                    join tp in context.TipoProyecto on p.IdTipoProyecto equals tp.IdTipoProyecto into TipoProyecto_join
                    from tp in TipoProyecto_join.DefaultIfEmpty()
                    let LiderPy =
                    (
                    from rp2 in context.RecursoProyecto
                    join recurso in context.Recurso on rp2.IdRecurso equals recurso.IdRecurso
                    where rp2.IdRol == 5 && rp2.IdProyecto == p.IdProyecto
                    select new RecursoDtoResponse
                    {
                        IdRecurso = recurso.IdRecurso,
                        Nombre = recurso.Nombre
                    }
                    ).FirstOrDefault()
                    where 
                    p.IdProyectoKMOLD != null && p.IdProyectoKMOLD != ""
                    select new GestionProyectoDtoReponse
                    {
                        IdProyecto = p.IdProyecto,
                        CodigoProyecto = p.IdProyectoKMOLD,
                        ProyectoEstado = est.Descripcion,
                        IdTipoProyecto = p.IdTipoProyecto,
                        TipoProyecto = tp.Descripcion,
                        IdEstadoProyecto = p.IdEstadoProyecto,
                        IdLiderProyecto = LiderPy != null ? LiderPy.IdRecurso : 0,
                        LiderProyecto = LiderPy != null ? LiderPy.Nombre : string.Empty,
                        IdJefeProyecto = r.IdRecurso,
                        JefeProyecto = r.Nombre,
                        Cliente = c.Descripcion,
                        Proyecto = p.Descripcion,
                        FechaFinImp = p.FechaFinImplantacion,
    FechaFin = p.FechaFinImplantacion == null ? "" : p.FechaFinImplantacion.value.toString("dd/MM/yyyy") IdCriticidad = p.IdCriticidad }).AsNoTracking().AsQueryable();

    The problem is that line:

    FechaFin = p.FechaFinImplantacion == null ? "" : p.FechaFinImplantacion.value.toString("dd/MM/yyyy")

    which generates an error.

    I need: FechaFin  to add after that a filter such as: where proyectos = proyectos.where(x => x.FechaFin.contains('21/01')

    Saturday, January 12, 2019 4:16 AM
  • User-893317190 posted

    Hi neoaguil17,

    As PatriceSc  has said, your  linq will be  translated to sql and  it couldn't  convert tostring("dd/MM/yyyy") to sql, you should directly use sql function to convert datetime to string.

    Below is my code.

      var strs = dbContext1.Entity1.Where(en => (en.mydate.Value == null ? "" : 
    SqlFunctions.DatePart("dd", en.mydate) + "/" // get the day using sqlfunction
    + SqlFunctions.DatePart("MM", en.mydate) + "/" // get the month using sqlfunction
    + SqlFunctions.DatePart("yyyy", en.mydate)) // get the year using sqlfunction
    .Contains("2018") // then you could use contains

    );

    var abc = strs.Select(en => new { time = en.mydate.Value == null ? "" : SqlFunctions.DatePart("dd", en.mydate) + "/" + SqlFunctions.DatePart("MM", en.mydate) + "/" + SqlFunctions.DatePart("yyyy", en.mydate) }).ToList(); GridView1.DataSource = abc; GridView1.DataBind();

    The result.

    But you should pay attention that   SqlFunctions.DatePart returns int?, if you want a string  09 for september  you should add  0 before it.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 14, 2019 2:43 AM