locked
Problem using IQueryable RRS feed

  • Question

  • User662762443 posted

    I have a query method that works, it brings the information from the database, however I am trying to use substring to get a part of a certain value from a table, I am using Iqueryable, but informs that it can not convert string to int, could anyone instruct me to fix the problem, I'm using nhibernate. Follow the code below:

    This is my select:

    select fo.year_exercise_ctx, fo.cod_font_group, fo.cod_fonte, fo.name_fonte,  
           ne.idt_document, ne.year_document, ne.cod_ug, ne.cod_document, substr (ne.cod_ranking, 54,4)  
      from lag4_es_2019.spa_fonte fo  
      left join lag4_es_2019.spe_ne ne  
         on fo.cod_group_fonte = substr (ne.cod_ranking, 54,1) and fo.cod_fonte = substr (ne.cod_ranking, 56,2)  
      where fo.cod_font_font not in (0) and fo.cod_font not in (00)  
         order by fo.cod_group_fonte, fo_cod_fonte, ne.cod_ug, ne.cod_document;  
    public static IQueryable <EmpenhoDTO>ListAll
            {
                ISessionFactory session = FluentHelper.GetSessionFactory (EnumDatabase.MsSqlSiplag);
                IQueryable <criterion = session.OpenSession ()
                    .CreateCriteria <EmpenhoDTO> ("ne")
                    .List <EmpenhoDTO> (). AsQueryable ()
                    .Select ((i, x) => new {x, str = i.CodSubscription.Substring ("54,4", x)});
    
                
    
                var aa = criterion; //Criterio.List<EmpenhoDTO> (). ToList ();
                return null;
            }

    I tried to implement the condition without success.

     public static IQueryable<EmpenhoDTO> ListAll()
            {
                ISessionFactory session = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlLag);
                IQueryable<EmpenhoDTO> criteria = session.OpenSession()
                    .CreateCriteria<EmpenhoDTO>("ne")
    
                    .List<EmpenhoDTO>().AsQueryable()
                    .Where(g => g.Codranking.Contains(g.Codranking.Substring(54, 4), g.Codranking.Substring(54, 1), g.Codranking.Substring(56, 2)));
                    //.Select((i, x)=> new {x, str = i.CodClassificacao.Substring("54,4", x) });
    
                var aa = criteria; //criterio.List<EmpenhoDTO>().ToList();
                return null;
            }
    using Domain.Lag.Domain;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace Domain.Lag.DataAccess.DTO
    {
        public class EmpenhoDTO
        {
            //public string CodEmpenho { get; set; }
            public virtual string CodClientCTX { get; set; }
            public virtual string IdtDocument { get; set; }
            public virtual string YearExerciseCTX { get; set; }
            public virtual string CodRanking { get; set; }
            public virtual string CodDocument { get; set; }
            public virtual string CodUg { get; set; }
            public virtual string YearDocument { get; set; }
    
    
            public override bool Equals(object obj)
            {
                //return AnoExercicio.ToString() == CodCliente.ToString() == CodGrupoFonte.ToString() == CodFonte.ToString();
                if (!(obj is EmpenhoDTO)) return false;
    
                var objA = obj as EmpenhoDTO;
                return (this.YearExerciseCTX == objA.YearExerciseCTX && this.CodClientCTX == objA.CodClientCTX && this.IdtDocument == objA.IdtDocument && 
                    this.CodDocument ==objA.CodDocument && this.CodUg==objA.CodUg && this.YearDocument ==objA.YearDocument );
                           
    
            }
    
            public override int GetHashCode()
            {
                return base.GetHashCode();
            }
        }
    }
    



    Tuesday, February 12, 2019 1:19 PM

Answers

  • User753101303 posted

    A string starts at index 0 so I believe your current places are wrong. It seems you just want to extract strings from CodRanking and that the Where clause was just an attempt for doing that ???? So I believe that for now a console app demo for what you want to could be quite close from :

    using System;
    using System.Collections.Generic;
    using System.DirectoryServices.AccountManagement;
    using System.Linq;
    
    namespace ConsoleDemo
    {
        class Program
        {
            public class Empenho // Actual db data
            {
                public virtual string CodRanking { get; set; }
            }
            public class EmpenhoDTO // This is your DTO
            {
                public virtual string CodRanking { get; set; }
                public virtual string Part1 { get; set; }
                public virtual string Part2 { get; set; }
                //public virtual string Part3 { get; set; }
            }
    
            public static IEnumerable<EmpenhoDTO> ListAll()
            {
                // Sample data source, doesn't seems the problem ?
                var criterio = new List<Empenho> { new Empenho() {CodRanking= "20.44.901.10.302. 0030. 2185.54.320120.0000. E0000.0.1.35.000003.3.3.90.48.000927.000000.000000. 00000000" } };
                var item = (from obj in criterio
                            select new EmpenhoDTO()
                            {
                                CodRanking = obj.CodRanking,
                                // YOU WANT TO EXTRACT VALUES FROM CODRANKING ?
                                Part1 = obj.CodRanking.Substring(53, 1), 
                                Part2 = obj.CodRanking.Substring(55, 2),
                                // Not sure what you want for this one ?
                            });
                            // I'm starting to believe you just don't want a Where clause ?
                return item;
            }
            static void Main(string[] args)
            {
                foreach(var item in ListAll())
                {
                    Console.WriteLine("{0}, {1}", item.Part1, item.Part2); // Shows 1, 35
                }
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 13, 2019 3:02 PM

All replies

  • User753101303 posted

    Hi,

    The type of Codranking is ? I'm not sure to see which Contains method you try to call (with 3 parameters ?). Also at some point it seems you call string.SubString with a "54,4" string ??? (which would better match the error message you are talkin about ?)

    I don't understand which criteria you are trying to write.

    Not directly releated but from a db design point of view, it is usally simpler to gather information from multiple columns rather than to split a single column into multiple pieces.

    Tuesday, February 12, 2019 5:21 PM
  • User303363814 posted

    .Substring("54.4", x)

    The first parameter needs to be an int.

    Tuesday, February 12, 2019 10:48 PM
  • User1724605321 posted

    Hi rtaVix ,

    What is the requirement ? Another point is that It seems you need to use "where in " in linq , something like :

    int[] VisitorIds = new int[] {1,2,3}; //an array to check with
    
        .Where(x => vivitorIds.Contains(x.Index)).ToList();

    Best Regards,

    Nan Yu

    Wednesday, February 13, 2019 2:03 AM
  • User662762443 posted

    Hello Nan Yu, how long, okay? Nan Yu, in that part of my code:

    public static IQueryable<EmpenhoDTO> ListAll()
            {
                ISessionFactory session = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlLag);
                IQueryable<EmpenhoDTO> criteria = session.OpenSession()
                    .CreateCriteria<EmpenhoDTO>("ne")
    
                    .List<EmpenhoDTO>().AsQueryable();
                    
    
                var aa = criteria; //criterio.List<EmpenhoDTO>().ToList();
                return null;
            }

    I can bring the database data for my select. However in codification I own for example:

    66568787754688978100

    69845455546888868287

    65659955548889865455

    suppose I have to use my select that I posted just above, I'm working with substring, my only problem is that I can not get this data in a nutshell, it even picks up and when I debug I see that it manages to bring information from the banks, however it brings me 66568787754688978100000 00000 000 one example only, I do not want to get all those numbers, even because I'm working with fluent.

    And then I'm going to load a list that I'll have to display in a Box Box, ug, document, and the document number.

    Wednesday, February 13, 2019 10:17 AM
  • User662762443 posted

    PaulTheSmith, but how do I do to work with those 3 substring as I showed in my select above? In this part of my code I can bring the information from the bank:

    public static IQueryable<EmpenhoDTO> ListAll()
            {
                ISessionFactory session = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlLag);
                IQueryable<EmpenhoDTO> criteria = session.OpenSession()
                    .CreateCriteria<EmpenhoDTO>("ne")
    
                    .List<EmpenhoDTO>().AsQueryable();
                    
                var aa = criteria; //criterio.List<EmpenhoDTO>().ToList();
                return null;
            }

    I put it returning null and I created a variable to view the results in the debug, however I will have to return the criteria for the list, however in my select I need to work with those substring that you viewed just above my query that I am using, but not I'm able to implement this condition.

    Wednesday, February 13, 2019 10:22 AM
  • User662762443 posted

    Hi PatriceSc, as I mentioned to the other colleagues my select in that part of my code:

    public static IQueryable<EmpenhoDTO> ListAll()
            {
                ISessionFactory session = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlLag);
                IQueryable<EmpenhoDTO> criteria = session.OpenSession()
                    .CreateCriteria<EmpenhoDTO>("ne")
    
                    .List<EmpenhoDTO>().AsQueryable();
                   
    
                var aa = criteria; //criterio.List<EmpenhoDTO>().ToList();
                return null;
            }

    It brings the results correctly, however in my query query there is a treatment that I need to implement these substring that you viewed just above, however I can not bring this data regarding the substring according to my query, I'm having difficulties in that part.

    Wednesday, February 13, 2019 10:26 AM
  • User753101303 posted

    Yes but the problem with :

     .Where(g => g.Codranking.Contains(g.Codranking.Substring(54, 4), g.Codranking.Substring(54, 1), g.Codranking.Substring(56, 2)));

    is that you have no Contains method with 3 parameters and it seems you are trying to test a value against some of its own parts ??? As it doesn't make sense it's hard to figure out which criteria you are trying to express this way. Or you want to test each of those substring parts against known values ??? Also the length seems to not match the length for those actual strings ?

    Edit: in short as the code seems quite far from what you really want it could be better to tell is in plain English which kind of criteria you are trying to apply. Or start with something simpler and change until it works? A sample usage for substring would be :

    .Where(g=>g.Codranking.Substring(2,2)=="56")) which shows all values with <digit><digit>56<other digits> code ranking...

    Edit 2 : and for the other fix try :

    .Select (o => new {o.CodSubscription,part1 = o.CodSubscription.Substring (2,2)});

    Does it work? If yes see how it works and move that step by step closest to what you actually want.

    Wednesday, February 13, 2019 11:50 AM
  • User662762443 posted

    I implemented the following situation without using contains.

    public static  IQueryable<EmpenhoDTO> ListAll(string dadosEmpenho = "")
            {
                ISessionFactory session = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlSiplag);
                IQueryable<EmpenhoDTO> criterio = session.OpenSession()
                    .CreateCriteria<EmpenhoDTO>()
                    .List<EmpenhoDTO>().AsQueryable()
                    .Where(g => g.CodRanking == g.CodRanking.Substring(54, 4) && g.CodRanking== g.CodRanking.Substring(54, 1) && g.CodRanking==g.CodRanking.
    Substring(56, 2));
    
                var aa = criterio; //criterio.List<EmpenhoDTO>().ToList();
                return null;
            }

    however I get the following error message:

    Enumeration did not produce results.

    Wednesday, February 13, 2019 12:01 PM
  • User753101303 posted

    Where ? This is an exception message ? I'm not using Nhibernate but I suspect you may have an exception on the db side because it seems you take characters from position 54 in a string that seems to have only 20 characters. Also your criteria doesn't make sense. You are comparing a string with a part of the same string ?

    So it would be like doing something such as "ABCD"=="B" && "ABCD"=="C" for example ie comparing a string to its 2nd and 3rd character which will never be true.

    Finally your method returns null and so you'll likely have anyway this error later. From your code I still don't understand which selection criteria you are trying to write.

    Wednesday, February 13, 2019 12:16 PM
  • User662762443 posted

    I was able to do the following:

    public static  IQueryable<EmpenhoDTO> ListAll(string dadosEmpenho = "")
            {
                ISessionFactory sessao = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlSiplag);
                IQueryable<EmpenhoDTO> criterio = sessao.OpenSession()
                    .CreateCriteria<EmpenhoDTO>()
                    .List<EmpenhoDTO>().AsQueryable();
                
    
                
                //IQueryable<EmpenhoDTO> empenho = Empenho.ListarTodos();
                //EmpenhoDTO empenho = new EmpenhoDTO();
                //var listEmpenho = empenho.;
                var item = (from obj in criterio
                            orderby obj.CodDocument
                            select new
                            {
                                CodRanking = obj.CodRanking,
                                yearDocument = obj.YearDocument,
                                codUg = obj.CodUg
                                
    
                            }).OrderBy(o => o.YearDocument).ToList();
                var aa = item; //criterio.List<EmpenhoDTO>().ToList();
    
                return null;
            }

    But I get CodRanking as follows:

    20.44.901.10.302. 0030. 2185.54.320120.0000. E0000.0.1.35.000003.3.3.90.48.000927.000000.000000. 00000000

    I need to get from position 54 to 27 the commitment that is symbolized by E0000.0.1.35 by eliminating the zeros and the dots, and then I need to get this information 1.35 that 1 = the Code Group Source and 35 is the source code, they will always come in this position, but the code numbers may change. So I have to see a good practice to capture these substring and I'm not getting it, looking at my code gives to do something?

    Wednesday, February 13, 2019 2:24 PM
  • User753101303 posted

    A string starts at index 0 so I believe your current places are wrong. It seems you just want to extract strings from CodRanking and that the Where clause was just an attempt for doing that ???? So I believe that for now a console app demo for what you want to could be quite close from :

    using System;
    using System.Collections.Generic;
    using System.DirectoryServices.AccountManagement;
    using System.Linq;
    
    namespace ConsoleDemo
    {
        class Program
        {
            public class Empenho // Actual db data
            {
                public virtual string CodRanking { get; set; }
            }
            public class EmpenhoDTO // This is your DTO
            {
                public virtual string CodRanking { get; set; }
                public virtual string Part1 { get; set; }
                public virtual string Part2 { get; set; }
                //public virtual string Part3 { get; set; }
            }
    
            public static IEnumerable<EmpenhoDTO> ListAll()
            {
                // Sample data source, doesn't seems the problem ?
                var criterio = new List<Empenho> { new Empenho() {CodRanking= "20.44.901.10.302. 0030. 2185.54.320120.0000. E0000.0.1.35.000003.3.3.90.48.000927.000000.000000. 00000000" } };
                var item = (from obj in criterio
                            select new EmpenhoDTO()
                            {
                                CodRanking = obj.CodRanking,
                                // YOU WANT TO EXTRACT VALUES FROM CODRANKING ?
                                Part1 = obj.CodRanking.Substring(53, 1), 
                                Part2 = obj.CodRanking.Substring(55, 2),
                                // Not sure what you want for this one ?
                            });
                            // I'm starting to believe you just don't want a Where clause ?
                return item;
            }
            static void Main(string[] args)
            {
                foreach(var item in ListAll())
                {
                    Console.WriteLine("{0}, {1}", item.Part1, item.Part2); // Shows 1, 35
                }
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 13, 2019 3:02 PM
  • User662762443 posted

    Friend through your example I was able to do what I was needing, it worked in parts because as I'm using IQueryable, I can not return ruturn item. gives this error:

    Cannot implicity convert type 'System.Collection.Generic.List<<Anonymous type: string codRank, string YearDocument, string codUg>> 'to 'System.Linq.Iquerable.

     public static IQueryable<EmpenhoDTO> ListAll(string dadosEmpenho = "")
            {
                ISessionFactory session = FluentHelper.GetSessionFactory(EnumDatabase.MsSqlSiplag);
                IQueryable<EmpenhoDTO> criterio = session.OpenSession()
                    .CreateCriteria<EmpenhoDTO>()
                    .List<EmpenhoDTO>().AsQueryable();
                
              
                var item = (from obj in criterio
                            orderby obj.CodDocument
                            select new
                            {
                                CodRank = obj.CodRank.Substring(53,4),
                                YearDocument= obj.YearDocument,
                                codUg = obj.CodUg,
                                
    
            }).OrderBy(o => o.YearDocument).ToList();
    
                return item;
            }

    Wednesday, February 13, 2019 5:44 PM
  • User753101303 posted

    You could use AsQueryable again as you have done previously :

    .OrderBy(o => o.YearDocument).AsQueryable(); // rather than ToList();

    Edit: ah and you are using an anonymous type which doesn't match your function return type which is IQueryable<EmpenhoDTO>. See my earlier sample :
    - I'm using a Empenho class that would expose all data available from the database
    - I'm selecting columns to create explictely a new EmphoDTO object that contains the properties I want to expose (see Edit2 as well)

    I gave a closer look at NHibernate and though this is not for now a huge concern, note that you are retrieving data and then only select which columns you are interested in. If using your previous code with the Where clause you would likely retrieve ALL rows and then only on the web server side you would filter them.

    IMO you should have a look at https://nhibernate.info/doc/nhibernate-reference/querylinq.html that shows how to use "Linq to NHibernate" to select columns or rows and have this processed on the database server rather than on the client side.

    Edit2 : and so this is likely NOT how I would have approached this. If "extracted data" are never needed in  queries my first thought would have been liiekly to add ready only properties that are just exposing parts of this CodRanking source string.

    My other option would have been to create computed columns (if your db have support for that) so that even on the db side I could have this kind of data handy without having to extract data explicitely again and again.

    Finally from a db design point of view, I would have created actual columns and would have exposed a single property or computed column that gathers all those pieces for convenience. For now you have to that because you are basically using a single column to store multiple values.

    Wednesday, February 13, 2019 6:10 PM
  • User662762443 posted

    PatriceSc, thank you for your explanation and teaching, I was able to make you carry all the effort, but in a rough way, because I could only carry this commitment depending on which source I choose in the Box combo, I can have 1 commitment to a source or more , I can bring in all source names, and commitment as I use the list all, would have an example of how I could do after selecting a source and loading the commit from that source using nhibernate / criteria?

    Friday, February 15, 2019 12:46 AM