Devart Forums

Posted: **Thu 12 Mar 2020 02:19**

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

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.

Posted: **Thu 12 Mar 2020 22:01**

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,
			  }

Posted: **Tue 17 Mar 2020 00:45**

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

Devart Forums

Could not format node 'Multiset' for execution as SQL

Could not format node 'Multiset' for execution as SQL

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

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