New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
NotImplementedException when filtering on a sub-query #4458
Comments
Well, HotChocolate generates some query expression, that linq2db cannot parse. To make a progress we need to know exact expression if HC could dump it for debug or have complete test setup to debug it. |
or your test code is enough to reproduce issue? |
My test code should be enough to reproduce the issue. |
I'm avare how LINQ translation works and I'm not sure that we can translate query that you have shown. Can you rewrite your query in the following way? var grouping =
from stock in connection.GetTable<WarehouseStock>()
group stock by new { stock.ItemId } into g
select new
{
g.Key.ItemId,
TotalAvailable = g.Sum(s => s.QuantityAvailable)
};
var query =
from item in connection.GetTable<Item>()
from stock in grouping
.LeftJoin(s => s.ItemId == item.Id)
select new ItemStockSummary()
{
ItemId = item.Id,
TotalAvailable = stock.TotalAvailable,
Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
}; Or via subquery: var query =
from item in connection.GetTable<Item>()
select new ItemStockSummary()
{
ItemId = item.Id,
TotalAvailable = connection.GetTable<WarehouseStock>()
.Where(s => s.ItemId == item.Id)
.Sum(s => s.QuantityAvailable),
Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
}; |
@sdanyliv thank you for the response. Please allow me to clarify; it's not the join onto This is fine: var query =
from item in connection.GetTable<Item>()
from stock in connection
.GetTable<WarehouseStock>()
.LeftJoin(s => s.ItemId == item.Id)
.GroupBy(s => s.ItemId)
select new ItemStockSummary()
{
ItemId = item.Id,
TotalAvailable = stock.Sum(s => s.QuantityAvailable),
Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
};
var unfiltered = async query.ToArrayAsync(); This is not: var query =
from item in connection.GetTable<Item>()
from stock in connection
.GetTable<WarehouseStock>()
.LeftJoin(s => s.ItemId == item.Id)
.GroupBy(s => s.ItemId)
select new ItemStockSummary()
{
ItemId = item.Id,
TotalAvailable = stock.Sum(s => s.QuantityAvailable),
Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
};
var filteredByScore = query.Where(i => i.Reviews.Any(r => r.Score > 95));
// ^ It's this filter that causes the exception to occur |
Well, I see, it is not translatable until version 6.0 arrive This version has much better LINQ translator and allows such tricks. @MaceWindu, maybe we can start publishing apha/beta/gamma packages for linq2db 6.0 to Azure Artifacts? |
@sdanyliv we can |
Any news on this? An expected release date? Or a preview NuGet package that I may use to assert whether or not version 6.0 fixes this problem? |
We expect to release first preview in a couple of weeks after parser PR merged to version_6 branch. |
Describe your issue
I'm trying to produce a query that gathers data from multiple tables (in the example provided below
Item
andWarehouseStock
) and projects that to an aggregate type (ItemStockSummary
), which is subsequently filtered by entries in a related table using a sub-query. This produces aNotImplementedException
.It worth noting that if I change the filter to also use a sub-query, it will work. However, that is not possible for me, as I use the HotChocolate GraphQL project in tandem with linq2db and let HotChocolate generate all my SQL queries based on the incoming GraphQL. The example provided here is a cleaned-up version of linq2db's debug output.
Steps to reproduce
I've allowed myself to provide a snippet that uses xunit and Testcontainers to spin up a test SQL database for ease of use. In order to run it as is, please install the following packages:
Environment details
Linq To DB
version: 5.4.0Database (with version): SQL Server 2017
ADO.NET Provider (with version): Microsoft.Data.SqlClient 4.8.5
Operating system: Ubuntu 22.04
.NET Version: 8.0.100 runtime, project uses .net7
The text was updated successfully, but these errors were encountered: