none
LINQ Query of User Enumeration Hangs RRS feed

  • Question

  • I have three DataTables, one populated from a .xlsx file, and the other two from .dbf files. I wanted to convert a FoxPro program that does a query of the files by loading into ADO.NET DataTables and then executing a LINQ To Dataset query of them.

    internal class PropTypeCone
    {
        public string RegNm { get; set; }
        public string Scenario { get; set; }
        public decimal Year { get; set; }
        public decimal Stock { get; set; }
        public decimal Compltns { get; set; }
        public decimal? AvailRate { get; set; }
        public decimal? NetAbsorp { get; set; }
        public decimal? TwRent { get; set; }
        public string MsaId { get; set; }
    }
     

    I populate my interim type like this:

     List<PropTypeCone> cones_list = (
        from frcA in dtForcA.AsEnumerable()
        from regmp in dtRegMp.AsEnumerable()
        from cit in dtCities.AsEnumerable()
        where frcA.Field<string>("msa_id") == regmp.Field<string>("mnemonic")
            && regmp.Field<string>("mnemonic") == cit.Field<string>("mnemonic")
            && cit.Field<int>("ind_sum") == 1
            && frcA.Field<decimal>("year") <= cur_yr
            && frcA.Field<decimal>("year") > 1980
            && frcA.Field<string>("scenario").Equals("Baseline")
        select new PropTypeCone
        {
            RegNm = regmp.Field<string>("reg_nm"),
            Year = frcA.Field<decimal>("year"),
            Stock = frcA.Field<int>("stock"),
            Compltns = frcA.Field<int>("compltns"),
            AvailRate = frcA.Field<decimal>("avail_rate"),
            NetAbsorp = frcA.Field<int>("net_absorp"),
            Rent = frcA.Field<decimal>("rent"),
            MsaId = frcA.Field<string>("msa_id"),
            Scenario = frcA.Field<string>("scenario")
        }).ToList();

    The strange thing is that the query takes about 4 minutes to execute, and then returns no results, though the equivalent FoxPro program - which executes much more quickly - does find result rows.


    - Mark Z.





    • Edited by Mark Zudeck Monday, November 12, 2012 4:46 PM
    Thursday, November 8, 2012 9:17 PM

Answers

  • Hi Mark,

    It’s strange. If the datatable fetch the data correctly, the query should be successful. Could you please check if you change the corresponding type (int=>decimal) in your query?

    select new PropTypeCone

        {

            RegNm = regmp.Field<string>("reg_nm"),

            Year = frcA.Field<decimal>("year"),

            Stock = frcA.Field<int>("stock"),

            Compltns = frcA.Field<int>("compltns"),

            AvailRate = frcA.Field<decimal>("avail_rate"),

            NetAbsorp = frcA.Field<int>("net_absorp"),

            Rent = frcA.Field<decimal>("rent"),

            MsaId = frcA.Field<string>("msa_id"),

            Scenario = frcA.Field<string>("scenario")

        }).ToList();

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Thursday, November 22, 2012 6:52 AM
    Wednesday, November 14, 2012 8:27 AM

All replies

  • Hi Mark,

    Welcome to the MSDN forum.

    I tested your code on my side, and it works greatly. I am not sure what type are dtForcA, dtRegMp and dtCities. In my code, I use below:

    var _ dtForcA = dtForcA.AsEnumerable();//The dtForcA is a datatable.

    var _ dtRegMp = dtRegMp.AsEnumerable();//The dtRegMp is a datatable.

    var _ dtCities = dtCities.AsEnumerable();//The dtCities is a datatable.

    Please check if fetch the data from a .xlsx and two .dbf successfully. Also, try to executes code one statement at a time by using F11 to check which line the program hangs on

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Monday, November 12, 2012 8:41 AM
  • Hi Alexander,

    Yes, all three of these are of type DataTable.  Before I execute my query, I print the rows of each of these DataTable objects, and I confirm that they have all been fetched correctly.

    I have modified my description above, because I now see that the query doesn't actually hang, but rather takes almost 5 minutes to execute.  In the debugger, I see that this happens when the query is actualized.  That is, either when I attempt to print the Enumeration in a foreach() loop, or if I try to make an actual List out of the result set in the query statement, as I have changed the description above to illustrate.

    After this long execution time, I now see that the resulting list is empty.  However, I know from the original FoxPro program that there should be records which meet the query criteria.

    Note that I was using an integer type for some of my class members, but have since discovered that the correct column type for them was decimal.  I've changed my original post to show that, too.  However, that fix didn't resolve the problem that I'm seeing.

    Please let me know if you require further information, such as full code and data files.

    Thanks.


    - Mark Z.





    • Edited by Mark Zudeck Monday, November 12, 2012 6:07 PM
    Monday, November 12, 2012 6:02 PM
  • Hi Mark,

    It’s strange. If the datatable fetch the data correctly, the query should be successful. Could you please check if you change the corresponding type (int=>decimal) in your query?

    select new PropTypeCone

        {

            RegNm = regmp.Field<string>("reg_nm"),

            Year = frcA.Field<decimal>("year"),

            Stock = frcA.Field<int>("stock"),

            Compltns = frcA.Field<int>("compltns"),

            AvailRate = frcA.Field<decimal>("avail_rate"),

            NetAbsorp = frcA.Field<int>("net_absorp"),

            Rent = frcA.Field<decimal>("rent"),

            MsaId = frcA.Field<string>("msa_id"),

            Scenario = frcA.Field<string>("scenario")

        }).ToList();

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Thursday, November 22, 2012 6:52 AM
    Wednesday, November 14, 2012 8:27 AM