[Core]: Re-enable the QueryRecursive<T> operation.

[mikependon]

The codes below must be supported.

Entity Class:

public class Customer
{
	public int Id { get; set; }
	public string AccountNo { get; set; }
	public string Name { get; set; }
	public IEnumerable<Order> Orders { get; set; }
}

Operation Call:

using (var connection = new SqlConnection(connectionString))
{
	var result = QueryRecursive<Customer>(c => c.Id == 10045);
}

[mikependon]

This is using the following SQLs:

SELECT C.Id AS [C.Id]
	, C.Name AS [C.Name]
	, O.Id AS [O.Id]
	, O.ProductId AS [O.ProductId]
	, ...
FROM [Customer] C
INNER JOIN [Order] O ON O.CustomerId = C.Id
WHERE C.Id == 10045;

The client application will then do the mappings accordingly.

[tola]

@mikependon This is a much needed feature that is not available in most of the ORM out there. I'm glad that you brought this in. Will this support multiple joins (more than 2 tables)? If so, is there any limit on the number of tables that can be joint?

[mikependon]

TBH, this is an initial feature that I removed, because I introduced the QueryMultiple and ExecuteQueryMultiple operation. However, there are some scenarios that this method needs to be introduced. I have to do recode of this one from scratch (all over again) - the old codes were purged already.

RE: No of tables that can be joint. Definitely, it can join multiple tables more than 2. There is a what we called cyclomatic problem here, as the child of the 5th table could be a parent of the 2nd table. In the old solution, you have to introduce the depth of the cycles. So the call would be something like this.

using (var connection = new SqlConnection(connectionString))
{
	var result = connection.QueryRecursive<Customer>(c => c.Id == 10045, depth = 5);
}

Or, could extend the existing one.

using (var connection = new SqlConnection(connectionString))
{
	var result = connection.Query<Customer>(c => c.Id == 10045, recursive = true, depth = 5);
}

[mdissel]

Why implement the loading with a join? You could minimize the duplicate column information in the result query by loading the information with a multi query like this:

SELECT C.* FROM [Customer] C where ID = 10045;
select o.* from [Order] O where exists (SELECT 1 FROM [Customer] C where ID = 10045 and o.Customer = c.Id)
--- repeated for each relation that should be loaded ---

Thanks

[mikependon]

@mdissel - thank you for this insight. It's been awhile since I wrote this enablement story. Anyway, QueryRecursive was first introduced in RepoDb before the Multiple Query. But I tend to remove it due to the fact that multiple query is a much more optimal as it is easier to control the implementations (developers POV). That's also what you recommended here.

The purpose of this story is all about splitting the query result into a Parent-Child classes, to make sure that the we can get the information needed with only a single SQL Execution. Though, it is being pushed by the community to us, but it is already pre-assessed by us and decided not to introduce it for now.

This is also the reason why we do not have JOINs in RepoDb up until now. The reason is, 99% of the problem is on the JOINs.

[mdissel]

But I do think it's a great addition if RepoDb has support for:

Query<Customer>(c => c.Id = 10045).Include(c => c.Orders).Include(c => c.Address)

and that is translated into sql by RepoDb into:

SELECT C.* FROM [Customer] C where ID = 10045;
select o.* from [Order] O where exists (SELECT 1 FROM [Customer] C where ID = 10045 and o.Customer = c.Id)
select a.* from [Address] A where exists (SELECT 1 FROM [Customer] C where ID = 10045 and a.Customer = c.Id)

or

Query<Customer>(c => c.Id = 10045).Include(c => c.Orders.Where(o => o.OrderDate.Year == 2020).Include(c => c.Address)

[mikependon]

@mdissel - it is a thing that is competing against Entity Framework which is really against our ideology. Actually, what you requested is can be done by QueryMultiple method. See the code below.

using (var connection = new SqlConnection(ConnectionString))
{
	var customerId = 10045;
	var result = connection.QueryMultiple<Customer, Orders, Address)(customer => customer.Id = customerId,
		order => order.CustomerId == customerId,
		address => address.CustomerId == customerId);
	var customer = result.Item1.FirstOrDefault();
	var orders = result.Item2.AsList();
	var address = result.Item3.AsList();
	
	// Process the 'customer', 'orders' and 'address' variables here
}

I guess, this is exactly what you are looking based on your query intentions. It will execute the following queries.

> SELECT * FROM [Customer] WHERE ID = 10045;
> SELECT * FROM [Order] WHERE CustomerId = 10045;
> SELECT * FROM [Address] WHERE CustomerId = 10045;

It just a different way, but it does the same. But is more optimal since you can control the hints (TBH). Hope that helps!

[mdissel]

Actually, what you requested is can be done by QueryMultiple method. See the code below.

Yes, my simple sample can be solved, but not if it's slightly more complex, like filtering on a customer property that is not part of the order or address entity.

var result = connection.QueryMultiple<Customer, Orders, Address)(customer => customer.Name == "Test",
		order => order.Customer.Name == "Test" ,
		address => address.Customer.Name == "Test");

[mikependon]

Yes, you are correct there. What the QueryMultiple can solve is N+1 via FK. Your scenario can then be solved by the ExecuteQueryMultiple, but it is on your discretion to write SQL like Dapper.