Devart Forums

Paul_Ibis

Create the following table in SQL

CREATE TABLE [dbo].[Test](
	[TestID] [int] IDENTITY(1000,1) NOT NULL,
	[PrimaryKey]  AS ([TestID]),
	[Code] [varchar](50) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[TestID] ASC
)ON [PRIMARY])
GO
INSERT INTO Test(code)
SELECT 'test'

Then run this code :

Code: Select all

IQueryable<Test> tester = sc.DB.Test.Where(p => p.Code == "test");
 List<int?> accs = tester.Select(p => (int?)p.PrimaryKey).ToList();

You get the error on the second line. Note that if this is changed to

Code: Select all

 List<int?> accs = tester.Select(p => (int?)p.TestID).ToList();

you still get the same error.

This is the definition

Code: Select all

[Table(Name = @"dbo.Test")]
    public partial class Test : INotifyPropertyChanging, INotifyPropertyChanged
    {

        private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(System.String.Empty);
        #pragma warning disable 0649

        private int _TestID;

        private int _PrimaryKey;

        private string _Code;
        #pragma warning restore 0649

        #region Extensibility Method Definitions

        partial void OnLoaded();
        partial void OnValidate(ChangeAction action);
        partial void OnCreated();
        partial void OnTestIDChanging(int value);
        partial void OnTestIDChanged();
        partial void OnPrimaryKeyChanging(int value);
        partial void OnPrimaryKeyChanged();
        partial void OnCodeChanging(string value);
        partial void OnCodeChanged();
        #endregion

        public Test()
        {
            OnCreated();
        }

    
        /// <summary>
        /// There are no comments for TestID in the schema.
        /// </summary>
        [Column(Storage = "_TestID", AutoSync = AutoSync.OnInsert, CanBeNull = false, DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true, IsPrimaryKey = true)]
        public int TestID
        {
            get
            {
                return this._TestID;
            }
            set
            {
                if (this._TestID != value)
                {
                    this.OnTestIDChanging(value);
                    this.SendPropertyChanging("TestID");
                    this._TestID = value;
                    this.SendPropertyChanged("TestID");
                    this.OnTestIDChanged();
                }
            }
        }

    
        /// <summary>
        /// There are no comments for PrimaryKey in the schema.
        /// </summary>
        [Column(Storage = "_PrimaryKey", AutoSync = AutoSync.Always, CanBeNull = false, DbType = "INT NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]
        public int PrimaryKey
        {
            get
            {
                return this._PrimaryKey;
            }
            set
            {
                if (this._PrimaryKey != value)
                {
                    this.OnPrimaryKeyChanging(value);
                    this.SendPropertyChanging("PrimaryKey");
                    this._PrimaryKey = value;
                    this.SendPropertyChanged("PrimaryKey");
                    this.OnPrimaryKeyChanged();
                }
            }
        }

    
        /// <summary>
        /// There are no comments for Code in the schema.
        /// </summary>
        [Column(Storage = "_Code", DbType = "VARCHAR(50)", UpdateCheck = UpdateCheck.Never)]
        public string Code
        {
            get
            {
                return this._Code;
            }
            set
            {
                if (this._Code != value)
                {
                    this.OnCodeChanging(value);
                    this.SendPropertyChanging("Code");
                    this._Code = value;
                    this.SendPropertyChanged("Code");
                    this.OnCodeChanged();
                }
            }
        }
   
        public event PropertyChangingEventHandler PropertyChanging;

        public event PropertyChangedEventHandler PropertyChanged;

        protected virtual void SendPropertyChanging()
        {
            var handler = this.PropertyChanging;
            if (handler != null)
                handler(this, emptyChangingEventArgs);
        }

        protected virtual void SendPropertyChanging(System.String propertyName) 
        {
            var handler = this.PropertyChanging;
            if (handler != null)
                handler(this, new PropertyChangingEventArgs(propertyName));
        }

        protected virtual void SendPropertyChanged(System.String propertyName)
        {
            var handler = this.PropertyChanged;
            if (handler != null)
                handler(this, new PropertyChangedEventArgs(propertyName));
        }
    }

Paul_Ibis

I haven't created a project, but I have managed to track down the issue, and it is quite specific, and not what I initially thought was the issue.

Consider the following simple table

Code: Select all

CREATE TABLE [dbo].[Test](
	[TestID] [int] IDENTITY(1,1) NOT NULL,
	[TestText] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[TestID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

and model

Code: Select all

public class RptTest
    {
        public int TestID { get; set; }
        public bool Thing { get; set; }
    }

Then look at this query, where DB is the DataContext

Code: Select all

var test = (from t in sc.DB.Test
                       select new RptTest()
                       {
                           TestID = t.TestID,
                           Thing = true
                       }).Distinct();

            var test2 = (from t in sc.DB.Test
                         select new RptTest()
                         {
                             TestID = t.TestID,
                             Thing = false
                         });


            var test3 = test.Union(test2).ToList();

This fails with the error message "Specified method is not supported" and does not produce any SQL.
If you remove the Distinct() on the first query, it runs.
If you remove setting the property "Thing" and leave the Distinct() it also runs.
if you change

Code: Select all

var test3 = test.Union(test2).ToList()

to

Code: Select all

var test3 = test.ToList().Union(test2.ToList()).ToList();

it will also run.
I have not tested the returned data to check if it is returning what is expected though.

Please let me know if you can reproduce this.

Paul_Ibis

Devart - Have you reproduced this ?

Paul_Ibis

I think I can see the possible issue. PrimaryKey in the underlying table is a computed column that was added to legacy tables in the database to conform to a new naming convention that was applied some time later.
So the create table in SQL would be something like this

Code: Select all

CREATE TABLE [dbo].[AccountTest]
(
	[AccountID] [int] IDENTITY(1000,1) NOT NULL,
	[PrimaryKey]  AS ([AccountID]),
	CONSTRAINT [PK_AccountTest] PRIMARY KEY CLUSTERED 
	(
		[AccountID] ASC
	)
)

if I change this

Code: Select all

 List<int?> accs = internalAccounts.Select(p => (int?)p.PrimaryKey).ToList();

to select the AccountID instead, I still get the same error message.
The declaration for the AccountID column is as below

Code: Select all

[[Column(Storage = "_AccountID", AutoSync = AutoSync.OnInsert, CanBeNull = false, DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true, IsPrimaryKey = true)]

The declaration for the Primarykey column is as below

Code: Select all

 [Column(Storage = "_PrimaryKey", AutoSync = AutoSync.Always, CanBeNull = false, DbType = "INT NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]

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

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 !!

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'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 changed the Linq query to be as below, and it got a bit better

Code: Select all

var query = (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
                             group data by 1 into grp
                             select new AnalyserModel.DataRow()
                             {
                                 RowCaption = "All data",
                                 Pax1 = grp.Where(q => q.ProductDate >= dates.StartDate1 && q.ProductDate <= dates.EndDate1).Sum(q => q.Pax),
                                 Gross1 = grp.Where(q => q.ProductDate >= dates.StartDate1 && q.ProductDate <= dates.EndDate1).Sum(q => q.Gross),
                                 Net1 = grp.Where(q => q.ProductDate >= dates.StartDate1 && q.ProductDate <= dates.EndDate1).Sum(q => q.Net),
                                 Pax2 = grp.Where(q => q.ProductDate >= dates.StartDate2 && q.ProductDate <= dates.EndDate2).Sum(q => q.Pax),
                                 Gross2 = grp.Where(q => q.ProductDate >= dates.StartDate2 && q.ProductDate <= dates.EndDate2).Sum(q => q.Gross),
                                 Net2 = grp.Where(q => q.ProductDate >= dates.StartDate2 && q.ProductDate <= dates.EndDate2).Sum(q => q.Net),
                             });

This now produces this error message : "'dbo.AnalysisBookings.ProductDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Looking at the SQL produced as below, the problem is in the first sub query. It should only have the SUM([t25].[Pax]) AS [C1] row. Those other rows should not be in there. Remove them and the whole thing works as expected.

Code: Select all

SELECT (
    SELECT SUM([t25].[Pax]) AS [C1], 
			[t25].[ProductDate], 
			[t25].[Gross], 
			[t25].[Net], 
			[t25].[Pax]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([t25].[ProductDate] >= @p5) AND ([t25].[ProductDate] <= @p6)
    ) AS [C1], 
	(
    SELECT SUM([Gross]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p7) AND ([ProductDate] <= @p8)
    ) AS [C2], (
    SELECT SUM([Net]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p9) AND ([ProductDate] <= @p10)
    ) AS [C3], (
    SELECT SUM([Pax]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p11) AND ([ProductDate] <= @p12)
    ) AS [C4], (
    SELECT SUM([Gross]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p13) AND ([ProductDate] <= @p14)
    ) AS [C5], (
    SELECT SUM([Net]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p15) AND ([ProductDate] <= @p16)
    ) AS [C6]
FROM [dbo].[AnalysisBookings] [t1]
INNER JOIN [dbo].[Product] [t2] ON [t1].[Fk_Product] = [t2].[PrimaryKey]
INNER JOIN [dbo].[Account] [t3] ON [t1].[Fk_Account_Agent] = [t3].[fk_Contact]
INNER JOIN [dbo].[Contact] [t4] ON [t3].[fk_Contact] = [t4].[PrimaryKey]
WHERE ((([t1].[ProductDate] >= @p0) AND ([t1].[ProductDate] <= @p1)) OR (([t1].[ProductDate] >= @p2) AND ([t1].[ProductDate] <= @p3))) AND ((([ProductDate] >= @p0) AND ([ProductDate] <= @p1)) OR (([ProductDate] >= @p2) AND ([ProductDate] <= @p3))) AND (@p4 = @p4)

Paul_Ibis

So this is the workaround I came up with. Note that I had to convert everything to lists so that the String.Join then worked. I also had to convert the main query into a list as well to be able to join on the summaries.
The most important thing was adding a .ToList() before the GroupBy in the first query
Without this it's a very bad idea, since it fires off a select query for every single line in the result of itemGroups
i.e. this code will fire a Select query off for every line

Code: Select all

var itemSummaries = (from i in itemGroups
                           select new
                          {
                              PosTransID = i.Key,
                              ItemSummary = string.Join(", ", i.Select(p => p.Units.ToString() + "x" + p.ItemDesc)),
                              UnitsSum = i.Sum(p=>p.Units)
                          }).ToList();

However, this does mean that there is a lot more data loaded into memory than before.

Is there a better way to do this ??

Note that LinqtoSQL will give a more informative errror : "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator" if you try and join a local list to the query.
LinqConnect gives a more mysterious error : "Constant cannot be sequences"

Code: Select all

var itemGroups = (from trans in posTransactions
				  join pti in sc.DB.PosTransItem on trans.PrimaryKey equals pti.Fk_PosTransaction
				  select new
				  {
						PosTransID = pti.Fk_PosTransaction,
						pti.ItemDesc,
						pti.Units
				   })
				   .ToList() //if this is not ToList(), it will run a separate select for every line in the result in the next query
				   .GroupBy(p => p.PosTransID)
				   .ToList(); //if this is not ToList(), it will just fall over with the same error as before in the next query.

var itemSummaries = (from i in itemGroups
			 select new
			  {
				  PosTransID = i.Key,
				  ItemSummary = string.Join(", ", i.Select(p => p.Units.ToString() + "x" + p.ItemDesc)),
				  UnitsSum = i.Sum(p=>p.Units)
			  });


var test = (from trans in sc.DB.PosTransaction.Where(p => p.TransDateTime >= model.StartDateTime
                                                                  && p.TransDateTime <= model.EndDateTime)
                          select new 
                          {
                              PosTransID = trans.PosTransID,
                          }).ToList();
						  
model.Data =   from sale in test
                          join summary in itemSummaries on sale.PosTransID equals summary.PosTransID
			  select new
			  {
				PosTransID = sale.PosTransID,
				ItemSummary = summary.ItemSummary,
			  }

Paul_Ibis

....and another query that runs in LinqToSQl that falls over in LinqConnect

Code: Select all

var test = (from trans in sc.DB.PosTransaction.Where(p => p.TransDateTime >= model.StartDateTime
                                                                                        && p.TransDateTime <= model.EndDateTime)
                          select new 
                          {
                              PosTransID = trans.PosTransID,
                              ItemSummary = string.Join(", ", trans.PosTransItem.Select(p => p.Units.ToString() + "x" + p.ItemDesc)),
                          }).ToList();

This is summarising a bunch or rows in the PostransItem table that is linked to the PosTransaction table
In LinqToSQL this is the SQL that is created

Code: Select all

SELECT [t0].[PosTransID], ((CONVERT(NVarChar,[t1].[Units])) + @p2) + [t1].[ItemDesc] AS [value], (
    SELECT COUNT(*)
    FROM [dbo].[PosTransItem] AS [t2]
    WHERE [t2].[PosTransID] = [t0].[PosTransID]
    ) AS [value2]
FROM [dbo].[PosTransaction] AS [t0]
LEFT OUTER JOIN [dbo].[PosTransItem] AS [t1] ON [t1].[PosTransID] = [t0].[PosTransID]
WHERE ([t0].[TransDateTime] >= @p0) AND ([t0].[TransDateTime] <= @p1)
ORDER BY [t0].[PosTransID], [t1].[PosItemID]

in LinqConnect it just falls over with the error that is in the subject.

What is the suggested workaround for this one ? Create another query and create the string in the code ? It's getting pretty frustrating trying to move over to LinqConnect as a large proportion of our existing code seems to need to be rewritten. I appreciate that it's not the most performant SQL that is produced, but it currently works and we have had no issues with significant slowness of the app with the code as is.

Paul_Ibis

This will work if the linq query is changed to

Code: Select all

var test = (from trans in sc.DB.PosTransaction.Where(p => p.TransDateTime >= model.StartDate
                                                     && p.TransDateTime < model.EndDateNextDay)
                        join payment in sc.DB.PosTransPayment on trans.PosTransID equals payment.PosTransID
                        from loc in sc.DB.Location.Where(p => p.Fk_RetailLocation == trans.LocationID).DefaultIfEmpty()
                        select new 
                        {
                            PosTransID = trans.PosTransID
                        });

which produces the valid SQL

Code: Select all

SELECT [t1].[PosTransID]
FROM [dbo].[PosTransaction] [t1]
INNER JOIN [dbo].[PosTransPayment] [t2] ON [t1].[PosTransID] = [t2].[PosTransID]
LEFT OUTER JOIN [dbo].[Location] [t3] ON [t3].[fk_RetailLocation] = [t1].[LocationID]
WHERE ([t1].[TransDateTime] >= @p0) AND ([t1].[TransDateTime] < @p1)

However, it would be good if the original code as posted worked so we wouldn't have to go through the whole code base and re-write each similar query. I just had a quick look through the code base and there would be around 100 queries that would need to be re-written.

Paul_Ibis

And another issue I have found in migrating from LinqToSQL to LinqConnect. This one looks like it can't cope with the "DefaultIfEmpty" syntax when there is also a join in the Where clause rather than . This is quite a large legacy code base and it would be a pain to have to go through every query that is like this and rewrite it to a different left join syntax which I am presuming would work.
The LinqConnect page says : "LinqConnect was developed closely to LINQ to SQL and retains full compatibility with it." but I have only just started going through our code and have already found four instances where it isn't , so I'm getting a bit concerned now about what else I might find.

This is the query (note that there were a bunch more columns in the actual query)

Code: Select all

var test = (from trans in sc.DB.PosTransaction.Where(p => p.TransDateTime >= model.StartDate
                                                                                    && p.TransDateTime < model.EndDateNextDay)
                        from payment in sc.DB.PosTransPayment.Where(p => p.PosTransID == trans.PosTransID)
                        from loc in sc.DB.Location.Where(p => p.Fk_RetailLocation == trans.LocationID).DefaultIfEmpty()
                        select new 
                        {
                            PosTransID = trans.PosTransID
                        });

and this is the SQL that is created which is invalid

Code: Select all

SELECT [t1].[PosTransID]
FROM [dbo].[PosTransaction] [t1]
CROSS APPLY (
    SELECT NULL 
    FROM [dbo].[PosTransPayment] [t3]
    WHERE [t3].[PosTransID] = [t1].[PosTransID]
    ) [t2]
LEFT OUTER JOIN [dbo].[Location] [t4] ON [t4].[fk_RetailLocation] = [t1].[LocationID]
WHERE ([t1].[TransDateTime] >= @p0) AND ([t1].[TransDateTime] < @p1)

with the following error :
SqlException: No column name was specified for column 1 of 't2'.

for comparison, this is the valid SQL that LinqToSQL creates

Code: Select all

SELECT [t0].[PosTransID]
FROM [dbo].[PosTransaction] AS [t0]
CROSS JOIN [dbo].[PosTransPayment] AS [t1]
LEFT OUTER JOIN [dbo].[Location] AS [t2] ON [t2].[fk_RetailLocation] = ([t0].[LocationID])
WHERE ([t0].[TransDateTime] >= @p0) AND ([t0].[TransDateTime] < @p1) AND ([t1].[PosTransID] = [t0].[PosTransID])

Paul_Ibis

So the error seems to be that each of the separate queries - pickups, dropoffs and dropoffsSector uses the same numbering of parameters. So for example, in the first query, there are these two lines generated in parts of the SQL

Code: Select all

 WHEN [t1].[CustomerID] IS NULL THEN @p1
 ....
 WHERE ([t2].[AvailStartDateTime] >= @p7)

in the second query

Code: Select all

WHEN [t14].[CustomerID] IS NULL THEN @p7
....
AND ([t7].[BookStatusE] IN (@p0,@p1,@p2))

so I am assuming that when it tries to do a union of those two queries it fails, since the the parameters are completely different e.g. .in the first query @p1 = '' and in the second @p1 = 40. In the first query @p7 = ' 1 Jan 2020' and in the second @p7 = ''

The query can run successfully if each of the queries is turned into a list first and the union is then done in the code
e.g. change this

Code: Select all

var pickupsAndDropOffs = pickups.Union(dropoffs).Union(dropoffsSector).ToList();

to this

Code: Select all

var pickupsAndDropOffs = pickups.ToList().Union(dropoffs).ToList().Union(dropoffsSector).ToList();

However, I would expect LinqConnect to deal with this and create the union query in SQL. It could also lead to subtle errors if the parameter type in each of the queries was the same, then the query may run without error but it would be running with incorrect values for one of the queries.

Paul_Ibis

...and another error in code that worked in LinqToSQL, but doesn't work in LinqConnect
The full error that is shown is "System.NotSupportedException
HResult=0x80131515
Message=Specified method is not supported.
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>"
It's a pretty complex query, and I don't really want to post the whole thing here, so I have simplified it as below

Code: Select all

IQueryable<ServiceResAvail> serviceResAvailFilter = sc.DB.ServiceResAvail.Where(p => p.AvailStartDateTime >= model.StartDate && p.AvailStartDateTime < model.StartDate.AddDays(1));


IQueryable<ServiceResAvail> serviceResAvailFilter = sc.DB.ServiceResAvail.Where(p => p.AvailStartDateTime >= model.StartDate && p.AvailStartDateTime < model.StartDate.AddDays(1));
var pickups = from sra in serviceResAvailFilter
                          from sr in sc.DB.ServiceResource.Where(p => p.PrimaryKey == sra.ResourceID)
                           ...
                           select new RptResourceManifest()
                           {
                               ServiceId = sr.ServiceID,
                               ...
                           };
var dropoffsSector = (from sra in serviceResAvailFilter
                                  from sr in sc.DB.ServiceResource.Where(p => p.PrimaryKey == sra.ResourceID)
                                  .....
                                  select new RptResourceManifest()
                                  {
                                      ServiceId = sr.ServiceID,
                                      ....
                                  }).Distinct();
                                 
            var dropoffs = (from sra in serviceResAvailFilter
                            from sr in sc.DB.ServiceResource.Where(p => p.PrimaryKey == sra.ResourceID)
                            .....
                            select new RptResourceManifest()
                            {
                                ServiceId = sr.ServiceID,
                                .....
                            }).Distinct();


            var pickupsAndDropOffs = pickups.Union(dropoffs).Union(dropoffsSector).ToList();

The error is happening on the last line.

Devart Forums

Search found 20 matches

Re: Error running a select when converting to int?

Re: Specified Method is not Supported error when doing a Union

Re: Model properties set incorrectly from query

Re: Error running a select when converting to int?

Re: Model properties set incorrectly from query

Re: Model properties set incorrectly from query

Re: Model properties set incorrectly from query

Model properties set incorrectly from query

Re: Error running a query on multiple IQueryable results

Re: Could not format node 'Multiset' for execution as SQL

Could not format node 'Multiset' for execution as SQL

Re: DefaultIfEmpty with join in Where clause produces invalid SQL

DefaultIfEmpty with join in Where clause produces invalid SQL

Re: Specified Method is not Supported error when doing a Union

Specified Method is not Supported error when doing a Union