Hi, I guess that also won't work in other Linq to DB providers. Such a query can't be executed at once in SQL. A subselect in the select of an SQL statement cannot return more than one row per result set row (leads to the query runtime error shown in your *InvalidSqlTwo*).
Such a task would need to be separated in at least two SQL queries unless it is executed as a denormalizing join query leading to repeated car data rows for all the wheels of a car. One option would be to explicitly perform such an denormalizing join query in the Linq provider and then doing the required grouping and DTO creation in memory. And the *InvalidSqlThree* generates a parse error before actually executing the query: you cannot generate multiple columns within a subselect (at least not in SQL Server). And again: if that worked it would still be prone to the error "Subquery returned more than 1 value" if there is a customer with multiple orders. On 27 Mai, 17:38, Cameron Harris <[email protected]> wrote: > Hi all, > > I'm having a problem using NHibernate's LINQ provider. I have a class (Car) > that has a one way mapping to another (Wheel), but I can't find a way of > using the LINQ provider to select a DTO that looks like this: { string > CarName, IEnumerable<string> WheelNames } > > I have attached a test program for running this, but it outputs invalid > SQL: > select car0_.Name as col_0_0_, (select wheels1_.Manufacturer from Wheel > wheels1_ where car0_.Id=wheels1_.CarId) as col_1_0_ from Car car0_ > > I've also made a set of test cases that show several queries that I would > have expected to work, but all generate invalid > SQL:http://pastebin.com/Wezy5Y6aThe errors I get are for each pair of test > cases are as follows: > > *InvalidSqlOne* > select customer0_.CompanyName as col_0_0_, . as col_1_0_, orders1_.OrderId as > OrderId30_, orders1_.CustomerId as CustomerId30_, orders1_.EmployeeId as > EmployeeId30_, orders1_.OrderDate as OrderDate30_, orders1_.RequiredDate as > Required5_30_, orders1_.ShippedDate as ShippedD6_30_, orders1_.ShipVia as > ShipVia30_, orders1_.Freight as Freight30_, orders1_.ShipName as ShipName30_, > orders1_.ShipAddress as ShipAdd10_30_, orders1_.ShipCity as ShipCity30_, > orders1_.ShipRegion as ShipRegion30_, orders1_.ShipPostalCode as > ShipPos13_30_, orders1_.ShipCountry as ShipCou14_30_ from Customers > customer0_ inner join Orders orders1_ on > customer0_.CustomerId=orders1_.CustomerId > ----> System.Data.SqlClient.SqlException : Incorrect syntax near the > keyword 'as'. > > *InvalidSqlTwo* > select (select orders1_.OrderId from Orders orders1_ where > customer0_.CustomerId=orders1_.CustomerId) as col_0_0_ from Customers > customer0_ > ----> System.Data.SqlClient.SqlException : Subquery returned more than 1 > value. This is not permitted when the subquery follows =, !=, <, <= , >, >= > or when the subquery is used as an expression. > > *InvalidSQLThree* > select customer0_.CompanyName as col_0_0_, (select orders1_.OrderDate, > orders1_.ShippedDate from Orders orders1_ where > customer0_.CustomerId=orders1_.CustomerId) as col_1_0_ from Customers > customer0_ > ----> System.Data.SqlClient.SqlException : Only one expression can be > specified in the select list when the subquery is not introduced with EXISTS. > > Repro.zip > 2733KAnzeigenHerunterladen -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
