Model properties set incorrectly from query

[Paul_Ibis]

I'm just about tearing my hair out now.
Yet another issue and I'm not sure what is going on here.
We have a model which has similarly named properties Pax1, Pax2, Gross1, Gross2 etc.
I am trying to work around another issue I have posed in another thread, so have these two test queries (what's in the tables etc. doesn't really matter)

Code: Select all

 var query1 = (from data in analysisData
                             join prod in products on data.Fk_Product equals prod.PrimaryKey
                             join acc in accounts on data.Fk_Account_Agent equals acc.Fk_Contact
                             join cont in contacts on acc.Fk_Contact equals cont.PrimaryKey
                             where data.ProductDate >= dates.StartDate1 && data.ProductDate <= dates.EndDate1
                             group data by 1 into grp
                             select new AnalyserModel.DataRow()
                             {
                                 RowCaption = "All data",
                                 Pax1 = grp.Sum(q => q.Pax),
                                 Gross1 = grp.Sum(q => q.Gross),
                                 Net1 = grp.Sum(q => q.Net),
                             }).ToList();

                var query2 = (from data in analysisData
                             join prod in products on data.Fk_Product equals prod.PrimaryKey
                             join acc in accounts on data.Fk_Account_Agent equals acc.Fk_Contact
                             join cont in contacts on acc.Fk_Contact equals cont.PrimaryKey
                             where data.ProductDate >= dates.StartDate2 && data.ProductDate <= dates.EndDate2
                             group data by 1 into grp
                             select new AnalyserModel.DataRow()
                             {
                                 RowCaption = "All data",
                                 Pax2 = grp.Sum(q => q.Pax),
                                 Gross2 = grp.Sum(q => q.Gross),
                                 Net2 = grp.Sum(q => q.Net),
                             }).ToList();

When I run the code and look at the values in query2, Pax2, Gross2 and Net2 are all null, yet Pax1, Gross1 and Net1 ARE all set and they have the values that Pax2,Gross2 and Net2 should have. I thought I was going mad and checked and double checked. Those values are not set in this query, they are set in the previous query.

If I copy and paste the exact code into the previous version of the project running LinqToSQL, lo and behold, the values in each of the queries are set as expected.

I was wanting to combine those two queries into one. What I found was if I made the first two queries anonymous types instead of AnalyserModel.DataRow and combined them in the third query into AnalyserModel.DataRow, then it picked up the correct values.
What on earth is going on here ?
This is seriously denting any confidence I have in using this product when something like this can happen.

[Paul_Ibis]

I tried a workaround as below (i.e. anonymous types in first two queries so that the Pax1,Pax2 etc. values actually get set correctly) and came across yet another problem.
var query1 = (from data in analysisData
join prod in products on data.Fk_Product equals prod.PrimaryKey
join acc in accounts on data.Fk_Account_Agent equals acc.Fk_Contact
join cont in contacts on acc.Fk_Contact equals cont.PrimaryKey
where data.ProductDate >= dates.StartDate1 && data.ProductDate <= dates.EndDate1
group data by 1 into grp
select new
{
RowCaption = "All data",
Pax1 = grp.Sum(q => q.Pax),
Gross1 = grp.Sum(q => q.Gross),
Net1 = grp.Sum(q => q.Net),
Pax2 = 0,
Gross2 = 0,
Net2 = 0
});

var query2 = (from data in analysisData
join prod in products on data.Fk_Product equals prod.PrimaryKey
join acc in accounts on data.Fk_Account_Agent equals acc.Fk_Contact
join cont in contacts on acc.Fk_Contact equals cont.PrimaryKey
where data.ProductDate >= dates.StartDate2 && data.ProductDate <= dates.EndDate2
group data by 1 into grp
select new
{
RowCaption = "All data",
Pax1 = 0,
Gross1 = 0,
Net1 = 0,
Pax2 = grp.Sum(q => q.Pax),
Gross2 = grp.Sum(q => q.Gross),
Net2 = grp.Sum(q => q.Net),
});

var query3 = (from data1 in query1
join data2 in query2 on data1.RowCaption equals data2.RowCaption
select new AnalyserModel.DataRow()
{
RowCaption = data1.RowCaption,
Pax1 = data1.Pax1,
Gross1 = data1.Gross1,
Net1 = data1.Net1,
Pax2 = data2.Pax2,
Gross2 = data2.Gross2,
Net2 = data2.Net2,
}).ToList();
This is the SQL that was produced and is completely incorrect. The sums should be inside each of the individual select statements, not on the outside. To give an idea of how wrong this is, the correct Pax1 figure that LinqToSQL calculates is 219. The value that the Devart code comes up with is 108186 as it has basically done a cross join then summed.

Code: Select all

SELECT @p4 AS [RowCaption], SUM([t1].[Pax]) AS [C1], SUM([t1].[Gross]) AS [C2], SUM([t1].[Net]) AS [C3], SUM([t6].[Pax]) AS [C4], SUM([t6].[Gross]) AS [C5], SUM([t6].[Net]) AS [C6]
FROM (
    SELECT [t2].[Pax], [t2].[Gross], [t2].[Net]
    FROM [dbo].[AnalysisBookings] [t2]
    INNER JOIN [dbo].[Product] [t3] ON [t2].[Fk_Product] = [t3].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t4] ON [t2].[Fk_Account_Agent] = [t4].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t5] ON [t4].[fk_Contact] = [t5].[PrimaryKey]
    WHERE ([t2].[ProductDate] >= @p0) AND ([t2].[ProductDate] <= @p1)
    ) [t1]
INNER JOIN (
    SELECT [t7].[Pax], [t7].[Gross], [t7].[Net]
    FROM [dbo].[AnalysisBookings] [t7]
    INNER JOIN [dbo].[Product] [t8] ON [t7].[Fk_Product] = [t8].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t9] ON [t7].[Fk_Account_Agent] = [t9].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t10] ON [t9].[fk_Contact] = [t10].[PrimaryKey]
    WHERE ([t7].[ProductDate] >= @p2) AND ([t7].[ProductDate] <= @p3)
    ) [t6] ON @p4 = @p4

If I run this in LinqToSQL, this is what I get. The sums are correctly calculated in each of the sub queries

Code: Select all

SELECT CONVERT(Decimal(29,4),[t6].[value2]) AS [Pax1], [t6].[value22] AS [Gross1], [t6].[value3] AS [Net1], CONVERT(Decimal(29,4),[t13].[value2]) AS [Pax2], [t13].[value22] AS [Gross2], [t13].[value3] AS [Net2]
FROM (
    SELECT @p3 AS [value], [t5].[value] AS [value2], [t5].[value2] AS [value22], [t5].[value3]
    FROM (
        SELECT SUM([t4].[Pax]) AS [value], SUM([t4].[Gross]) AS [value2], SUM([t4].[Net]) AS [value3]
        FROM (
            SELECT @p0 AS [value], [t0].[ProductDate], [t0].[Pax], [t0].[Gross], [t0].[Net]
            FROM [dbo].[AnalysisBookings] AS [t0]
            INNER JOIN [dbo].[Product] AS [t1] ON [t0].[Fk_Product] = ([t1].[PrimaryKey])
            INNER JOIN [dbo].[Account] AS [t2] ON [t0].[Fk_Account_Agent] = [t2].[fk_Contact]
            INNER JOIN [dbo].[Contact] AS [t3] ON [t2].[fk_Contact] = ([t3].[PrimaryKey])
            ) AS [t4]
        WHERE ([t4].[ProductDate] >= @p1) AND ([t4].[ProductDate] <= @p2)
        GROUP BY [t4].[value]
        ) AS [t5]
    ) AS [t6]
INNER JOIN (
    SELECT @p7 AS [value], [t12].[value] AS [value2], [t12].[value2] AS [value22], [t12].[value3]
    FROM (
        SELECT SUM([t11].[Pax]) AS [value], SUM([t11].[Gross]) AS [value2], SUM([t11].[Net]) AS [value3]
        FROM (
            SELECT @p4 AS [value], [t7].[ProductDate], [t7].[Pax], [t7].[Gross], [t7].[Net]
            FROM [dbo].[AnalysisBookings] AS [t7]
            INNER JOIN [dbo].[Product] AS [t8] ON [t7].[Fk_Product] = ([t8].[PrimaryKey])
            INNER JOIN [dbo].[Account] AS [t9] ON [t7].[Fk_Account_Agent] = [t9].[fk_Contact]
            INNER JOIN [dbo].[Contact] AS [t10] ON [t9].[fk_Contact] = ([t10].[PrimaryKey])
            ) AS [t11]
        WHERE ([t11].[ProductDate] >= @p5) AND ([t11].[ProductDate] <= @p6)
        GROUP BY [t11].[value]
        ) AS [t12]
    ) AS [t13] ON [t6].[value] = [t13].[value]

The solution again, was to move everything into memory by creating the lists before the join i.e. run a ToList() on query1 and query2 .The other alternative is to rewrite the queries to get a unique ID on each line returned in query1 and query2. All up, that's a lot of hoops to jump through to get back to a result that is correct compared to LinqToSQL.

[Paul_Ibis]

I did a bit more investigation on the weirdness with setting values in multiple queries, and it doesn't appear to have anything to do with summing

Code: Select all

                List<AnalyserModel.DataRow> test1 = (from data in DB.AnalysisBookings.Where(p=>p.ProductDate >= dates.StartDate1 
                                                                                            && p.ProductDate <= dates.EndDate1)
                                                    select new AnalyserModel.DataRow()
                                                    {
                                                        RowCaption = "xxxx",
                                                        Pax1 = data.Pax,
                                                        Gross1 = data.Gross,
                                                        Net1 = data.Net,
                                                    }).ToList();

                List<AnalyserModel.DataRow> test2 = (from data in DB.AnalysisBookings.Where(p => p.ProductDate >= dates.StartDate2
                                                                                           && p.ProductDate <= dates.EndDate2)
                                                     select new AnalyserModel.DataRow()
                                                     {
                                                         RowCaption = "xxxx",
                                                         Pax2 = data.Pax,
                                                         Gross2 = data.Gross,
                                                         Net2 = data.Net,
                                                     }).ToList();

When looking at values via intellisense in the test2 list Pax2,Gross2 and Net2 are all not set but Pax1,Gross1 and Net1 are all set to values that should be in Pax2,Gross2 and net2.
If i then loop through and print out values from the list it is the same.
This is a serious issue !!

[Shalex]

What I found was if I made the first two queries anonymous types instead of AnalyserModel.DataRow and combined them in the third query into AnalyserModel.DataRow, then it picked up the correct values.

Give us a complete definition of the type we can use for reproducing the issue instead of AnalyzerModel.DataRow():

Code: Select all

	var query1 = (from data in context.analysisDatas
					join prod in context.products on data.FkProduct equals prod.PrimaryKey
					join acc in context.accounts on data.FkAccountAgent equals acc.FkContact
					join cont in context.contacts on acc.FkContact equals cont.PrimaryKey
					where data.ProductDate >= dates.StartDate1 && data.ProductDate <= dates.EndDate1
					group data by 1 into grp

					//select new AnalyserModel.DataRow()					
					select new YOUR_TYPE_FOR_REPRODUCING()
					
					{
						RowCaption = "All data",
						Pax1 = grp.Sum(q => q.Pax),
						Gross1 = grp.Sum(q => q.Gross),
						Net1 = grp.Sum(q => q.Net),
					}).ToList();

	var query2 = (from data in context.analysisDatas
					join prod in context.products on data.FkProduct equals prod.PrimaryKey
					join acc in context.accounts on data.FkAccountAgent equals acc.FkContact
					join cont in context.contacts on acc.FkContact equals cont.PrimaryKey
					where data.ProductDate >= dates.StartDate1 && data.ProductDate <= dates.EndDate1
					group data by 1 into grp
					
					//select new AnalyzerModel.DataRow()
					select new YOUR_TYPE_FOR_REPRODUCING()
					
					{
						RowCaption = "All data",
						Pax2 = grp.Sum(q => q.Pax),
						Gross2 = grp.Sum(q => q.Gross),
						Net2 = grp.Sum(q => q.Net),
					}).ToList();

[Paul_Ibis]

OK, so this is a full reproduction of the issue
Create a table in the database with no primary key (not sure if this is relevant) and fill with some random data

Code: Select all

CREATE TABLE [dbo].[Test](
	[Gross] [decimal](15, 6) NULL,
	[Net] [decimal](15, 6) NULL,
	[Pax] [int] NULL,
) ON [PRIMARY]
GO

create a model to hold data in the code

Code: Select all

public class TestModel
    {
        public string RowCaption { get; set; }
        public decimal? Pax1 { get; set; }
        public decimal? Gross1 { get; set; }
        public decimal? Net1 { get; set; }
        public decimal? Pax2 { get; set; }
        public decimal? Gross2 { get; set; }
        public decimal? Net2 { get; set; }
    }

Run the following

Code: Select all

                var query1 = (from data in DB.Test
                              group data by 1 into grp
                              select new TestModel
                              {
                                  RowCaption = "All Data",
                                  Pax1 = grp.Sum(q => q.Pax),
                                  Gross1 = grp.Sum(q => q.Gross),
                                  Net1 = grp.Sum(q => q.Net),
                                  Pax2 = 0,
                                  Gross2 = 0,
                                  Net2 = 0,
                              }).ToList();

                var query2 = (from data in DB.Test
                              group data by 1 into grp
                              select new TestModel
                              {
                                  RowCaption = "All Data",
                                  Pax1 = 0,
                                  Gross1 = 0,
                                  Net1 = 0,
                                  Pax2 = grp.Sum(q => q.Pax),
                                  Gross2 = grp.Sum(q => q.Gross),
                                  Net2 = grp.Sum(q => q.Net),
                              }).ToList();

look at the list created in the second code. There should be 1 line in it. Pax2, Gross2 and Net2 are all zero, but Pax1,Gross1 and Net1 are all set.
Change so the queries so that they use anonymous types as below

Code: Select all

                var query1 = (from data in DB.Test
                              group data by 1 into grp
                              select new 
                              {
                                  RowCaption = "All Data",
                                  Pax1 = grp.Sum(q => q.Pax),
                                  Gross1 = grp.Sum(q => q.Gross),
                                  Net1 = grp.Sum(q => q.Net),
                                  Pax2 = 0,
                                  Gross2 = 0,
                                  Net2 = 0,
                              }).ToList();

                var query2 = (from data in DB.Test
                              group data by 1 into grp
                              select new 
                              {
                                  RowCaption = "All Data",
                                  Pax1 = 0,
                                  Gross1 = 0,
                                  Net1 = 0,
                                  Pax2 = grp.Sum(q => q.Pax),
                                  Gross2 = grp.Sum(q => q.Gross),
                                  Net2 = grp.Sum(q => q.Net),
                              }).ToList();

and note that the Pax2,Gross2 and Net2 values are set as expected.

[Paul_Ibis]

Devart - Have you reproduced this ?

[Shalex]

Thank you for the detailed steps. We have reproduced the issue and are investigating it.