Question: How are joins supported when querying data from several tables

[mediasystems-ch]

I'm not sure how joins are supported in RepoDB when querying data from multiple tables. A typical scenario in my application is querying of parent child relationships without knowing any IDs. Is this basically possible?

[mediasystems-ch]

In dapper, this seems to be possible (example: https://www.learndapper.com/relationships). Is this similarly possible with RepoDB?

[mikependon]

We never had supported the JOIN and Sub Tables in RepoDB, therefore, we strongly recommend to just use the raw SQL execution and directly map it to an entity.

[mikependon]

Though, in the past, we have supported the recursive query, but we had eliminated due to the fact that JOINs are the 90% of the problem in an ORM in a complex scenarios. The splitOn on Dapper is a temporary remedy to process the distinction of the relationships within the memory.

For now, we will force you create your JOIN statement and map it directly to a model (like below).

var salesEmployees = connection.ExecuteQuery<Employee>("SELECT e.* FROM Employee E INNER JOIN Department D ON D.Id = E.DepartmentId WHERE D.Name = 'Sales';");

[mediasystems-ch]

If I understand correctly, I have to query each entity individually, right? But wouldn't it be more performant to do this in one query? Then only one round-trip to the database would be necessary.

[mikependon]

@mediasystems-ch - that's what we are trying to say, please avoid doing that as it is not performant. Instead, create your own model that accepts the resultset of the JOIN query raw execution.

You can do use the QueryMultiple or ExecuteQueryMultiple operations, but they are meant for different scenarios. Somehow, it could be a temporary remedy for your use-case.

[mediasystems-ch]

OK, sure, if I use a custom model class as the resultset, that should theoretically work. How does the mapping work here? I suppose I have to use aliases because of course multiple entities can have the same field names.

However, when I try to do this, I get the following exception when calling ExecuteQuery:

System.ArgumentNullException: 'Value cannot be null. Arg_ParamName_Name'

at System.Enum.TryParse(Type enumType, String value, Boolean ignoreCase, Boolean throwOnFailure, Object& result) at RepoDb.Reflection.DataReader.<ToEnumerable>d__01.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at RepoDb.DbConnectionExtension.ExecuteQueryInternalForType[TResult](IDbConnection connection, String commandText, Object param, Nullable1 commandType, String cacheKey, Nullable1 cacheItemExpiration, Nullable1 commandTimeout, IDbTransaction transaction, ICache cache, String tableName, Boolean skipCommandArrayParametersCheck) at RepoDb.DbConnectionExtension.ExecuteQuery[TResult](IDbConnection connection, String commandText, Object param, Nullable1 commandType, String cacheKey, Nullable1 cacheItemExpiration, Nullable1 commandTimeout, IDbTransaction transaction, ICache cache)

It seems that a value of a enum type is null. But when I execute the query manually every enum should have a value. How can I debug the this?

[mikependon]

Enum somehow is complex. It depends...

If your enum property is string then it will be saved as string.

if your enjm property is int then it will be saved as int, default to the first value of enum, unless it is nullable.

if your enum property is string and your table column is int, then you need to use type mapping to properly save the value.

if your enum property is nullable and if it is null in the table, then it is null by default.

---

I could not give more info until you share us the model and the schema. To speed up investigation, can you share me your model and schema? I will try to replicate it.

[mikependon]

In addition, it is good if you can share some SQL as well. To give you reference, below is a sample code.

Let us say you have the following tables.

CREATE TABLE [db].[Employee]
(
     [Id] INT IDENTITY NOT NULL
     , [DepartmentId] INT NOT NULL
     , [Name] NVARCHAR(256) NOT NULL
) ON PRIMARY;
GO

CREATE TABLE [dbo].[Department]
(
     [Id] INT IDENTITY NOT NULL
     , [Name] NVARCHAR(256) NOT NULL
) ON PRIMARY;

Then, we recommend to create a model below.

public class Employee
{
     public int Id { get; set; }
     public string Name { get; set; }
     public string Department { get; set; }
}

And do execute it like below.

var employees = connection.ExecuteQuery<Employee>(
	@"SELECT E.[Id]
		, E.[Name]
		, D.[Name] AS [Department]
	FROM [dbo].[Employee] E
	INNER JOIN [dbo].[Department] D ON D.[Id] = E.[DepartmentId]
	WHERE (D.[Name] = @DepartmentName",
                new { DepartmentName = "Sales" });

Most likely, you should use the alias to return the proper column name as exactly same with class model properties name.

[mediasystems-ch]

Thank you for the code examples. That's exactly how I did it. Only my tables and my SQL query are of course much more extensive than the example.

I was able to solve the problem in the meantime. It was not so easy because I use many enum types in the query. I had to comment out each one in the model and test again and again to find the culprit. Also, the exception error message "Value cannot be null. Arg_ParamName_Name" misled me a bit. I assumed that a column in the query had null values. But that was not the case. One of the many enum types was empty.

It would be helpful if the error message in such cases gave some indication of which property from the model or which field was involved. Then it would be much easier to find such mapping errors.

Thank you very much for your support.

[mikependon]

Referring to our Exception Handling policy, we are not doing any parsing or anything.

But this is something we can further improve if you can give us a very simple application that replicates the problem. I suggest, if you can spend something like 15 mins to create a very simple application that replicate this problem and upload it here, the better.

---

Btw, has this does not indicate which parameter is missing? I would assume it would say that this mapping is specific to a certain property/column. See below.

Value cannot be null. @columnName

[mediasystems-ch]

Sorry for not getting back to you for so long. I have currently quite a time pressure with my project.

Correct, the exception did not provide any clue as to which parameter was involved ("Value cannot be null. Arg_ParamName_Name"). A hint about which property/column it is would be really helpful.

As soon as I find time, I will put together a very simple sample application that can be used to reproduce the problem.