Devart Forums

Posted: **Thu 18 Jul 2013 20:27**

Hi,
I have a problem when I run a query that have "group by" in it.
I have the following code :

var horairesByDate = from item in ((App)Application.Current).db.Horaires
                                where item.Complete == false && item.Datefin >= 				startDate.Date && item.Datedebut < endDate.Date
                                     orderby item.Datedebut
                                     group item by item.Datedebut.Date
                                    into groupedByDate
                                    select groupedByDate;

                var myList = await horairesByDate.ToListAsync();
                //  var myList = await  horairesByDate.ToListAsync();
                foreach (var List in myList)
                {
                    var currentList = List;
                    foreach (var horaire in currentList)
                    {
                        System.Diagnostics.Debug.WriteLine(horaire.Datedebut.ToString() + " " + horaire.Datefin.ToString() + " ID : " + horaire.Id + " Evénement :  " + horaire.Evenement);
                    }
                }
GroupedItems.Source = horairesByDate;

This code ask data in a table between 2 dates and group them by date. It work well the first time I execute de query. If I ask the data between 2013-07-18 to 2013-07-20 I will get all the right data. In my application I can change the start and end date and execute de query again. If I change the date after I have execute the first query and I ask data between 2013-07-18 to 2013-07-22 and I execute the query again, I will only get the data between 2013-07-18 to 2013-07-20. If I execute my application again, but ask my first query for data between 2013-07-18 to 2013-07-22 it will work well. If I ask for the second query the data between 2013-07-18 to 2013-07-20 it will work well also, because 2013-07-20 is smaller than the end date (2013-07-22) that I asked for the first query, but If I ask after to get data between 2013-07-18 to 2013-07-25, I will only get data between 2013-07-18 and 2013-07-22 because it's the range I asked for my first query.
I have also the same query without grouping :

Code: Select all

    ((App)Application.Current).db = new TtvDevV1DataContext()
                bool exist = ((App)Application.Current).db.DatabaseExists();
                var query = from item in ((App)Application.Current).db.Horaires
                            where item.Complete == false && item.Datefin >= dateDebut && item.Datedebut < dateFin
                            orderby item.Datedebut
                            select item;
               var currentList = await query.ToListAsync();
                foreach (var horaire in currentList)
                {
System.Diagnostics.Debug.WriteLine(horaire.Datedebut.ToString() + " " + horaire.Datefin.ToString() + " ID : " + horaire.Id + " Evénement :  " + horaire.Evenement);
                }
                ItemGridView.ItemsSource = currentList;

With this query all is working fine! I can change my dates as I want and I always get the good result. I get the problem only with the query with "Group by"
I would like to know if you have any idea why I get this problem.

Thank you in advance!

Posted: **Mon 22 Jul 2013 09:57**

Thank you for the report. We have reproduced this issue. We will investigate it and inform you about the results as soon as possible.
As a workaround, please try disabling compiled query cache, for example remove (or comment) the next line in the YourDataContext.Designer.cs file:

Code: Select all

public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(YourDataContext));

However, when changing it manually in the *.Designer.cs file, these changes will be lost after making any changes to your model and regenerating the code. To prevent it, you could try modifying the LinqConnect template so that the line

Code: Select all

public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(YourDataContext));

is not generated in the *.Designer.cs file, e.g.:
- open Model Explorer->Templates;
- right-click the "LinqConnect" template and select "Copy to Model Folder" from the shortcut menu;
- remove or comment this line of code (line 85):

Code: Select all

public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(<#= contextClassName #>));

- save changes and re-generate the code for your model.

Posted: **Mon 05 Aug 2013 18:01**

Sorry for late answer, I am back from two more vacations weeks. I just try to remove the compiled query cache this morning and all it working fine without it. Do you plan to release a fix for this soon?

Thank you in advance!

Posted: **Wed 07 Aug 2013 11:38**

We are working on this issue. We will inform you about the results as soon as possible.

Posted: **Thu 08 Aug 2013 09:45**

We have made investigation and at the moment we can't tell any timeframe for the fix on this issue.
We recommend you to use one of these workarounds:
1) Disable compiled query cache as we have proposed you before.
2) Re-write your query like in this example:

Code: Select all

 TestMDataContext dt = new TestMDataContext();
            DateTime data1 = dateTimePicker1.Value.Date;
            DateTime data2 = dateTimePicker2.Value.Date;
           
            var dates = dt.Test_Dates
            .Where(item => item.data >= data1 && item.data <= data2)
            .Select(item => item.data.Date)
            .Distinct()
            .ToArray();

            var query = from item in dt.Test_Dates
                        where dates.Contains(item.data.Date)
                        orderby item.data
                        group item by item.data.Date
                            into groupedByDate
                            select groupedByDate;

            var myList = query.ToList();
            foreach (var List in myList)
            {
                var currentList = List;
                foreach (var horaire in currentList)
                {
                    richTextBox1.Text = richTextBox1.Text+ ("data:" + horaire.data.ToString() + " ID : " + horaire.id+"\n");
                }
            }

We will continue the investigation process and inform you about the results when any are available.

Devart Forums

Problem with query with Group By

Problem with query with Group By

Re: Problem with query with Group By

Re: Problem with query with Group By

Re: Problem with query with Group By

Re: Problem with query with Group By