Feature: Join support in POCO mode

[myabdolmaleki]

there is no method with join name in SqlConnection Class and BaseRepository and no any join support in SqlServerTableHints Class.

one of the best functionality of orms is support rdbms functionalities in native languages not just in raw sql methods.

please add joins like other perfect methods!

[mikependon]

Thank you for this. We are on top of it already. There will be detailed stories for this one soon.

[myabdolmaleki]

Thank you for this. We are on top of it already. There will be detailed stories for this one soon.

we need that, join and load related data.

thanks a lot.

[mikependon]

You can do it in a different way. That is a basic scenario for RepoDb. See this link: https://repodb.readthedocs.io/en/latest/pages/connection.html#querymultiple

It has recently been asked on our Gitter Chat.

Or, here is the code for you.

Querying Single Parent and its Children

using (var connection = new SqlConnection(@"Server=.;Database=Northwind;Integrated Security=SSPI;").EnsureOpen())
{
		// The parent Id
		var customerId = 10045;

		// Get the parent customer, and the child objects
		var result = connection.QueryMultiple<Customer, Order>(
				customer => customer.Id == customerId,
				order => order.CustomerId == customerId);

		// Read the customer
		var customer = result.Item1.FirstOrDefault();

		// Read the orders
		var orders = result.Item2.AsList();

		// Set the properties
		customer.Orders = orders;

		// More codes here
		....
}

Querying Multiple Parents and there Children

using (var connection = new SqlConnection(@"Server=.;Database=Northwind;Integrated Security=SSPI;").EnsureOpen())
{
		// The parent Ids
		var ids = new [] { 10045, 20012, 44190, ... };

		// Get the parent customers, and the child objects
		var result = connection.QueryMultiple<Customer, Order>(
				customer => ids.Contains(customer.Id),
				order => ids.Contains(order.CustomerId);

		// Read the customers
		var customers = result.Item1.AsList();

		// Set the children of each
		customers.ForEach(c => c.Orders = result.Item2.Where(o => o.CustomerId == c.Id));

		// More codes here
		....
}

[myabdolmaleki]

Tuple has limit. we need multiple join. we are developing banking solutions.

we are working on huge data in our organization.

we need join without limitation.

[myabdolmaleki]

You can do it in different way. That is a basic scenario for RepoDb. See this link: https://repodb.readthedocs.io/en/latest/pages/connection.html#querymultiple

It has recently been asked on our Gitter Chat.

Or, here is the code for you.

Querying Single Parent and its Children

using (var connection = new SqlConnection(@"Server=.;Database=Northwind;Integrated Security=SSPI;").EnsureOpen())
{
		// The parent Id
		var customerId = 10045;

		// Get the parent customer, and the child objects
		var result = connection.QueryMultiple<Customer, Order>(
				customer => customer.Id == customerId,
				order => order.CustomerId == customerId);

		// Read the customer
		var customer = result.Item1.FirstOrDefault();

		// Read the orders
		var orders = result.Item2.AsList();

		// Set the properties
		customer.Orders = orders;

		// More codes here
		....
}

Querying Multiple Parents and there Children

using (var connection = new SqlConnection(@"Server=.;Database=Northwind;Integrated Security=SSPI;").EnsureOpen())
{
		// The parent Ids
		var ids = new [] { 10045, 20012, 44190, ... };

		// Get the parent customers, and the child objects
		var result = connection.QueryMultiple<Customer, Order>(
				customer => ids.Contains(customer.Id),
				order => ids.Contains(order.CustomerId);

		// Read the customers
		var customers = result.Item1.AsList();

		// Set the children of each
		customers.ForEach(c => c.Orders = result.Item2.Where(o => o.CustomerId == c.Id));

		// More codes here
		....
}

it have performance issue

see following commands

exec sp_executesql N'SELECT [Id], [Name] FROM [Author] WHERE ([Id] = @T0_Id) ;
SELECT [Id], [Name], [Aid] FROM [Book] WHERE ([Aid] = @T1_Aid) ;
',N'@T0_Id int,@T1_Aid int',@T0_Id=1,@T1_Aid=1

its run 2 select command in sql server but we want just a select command with join.

we can write 2 select command in our codes.

your excellent micro orm needs load related data with hints, like left join, right join, inner join.

if you do that your repoDb in perfect and awesome and amazing too.

[myabdolmaleki]

i found a solution! but it dont create join command in sql server.

following classes are poco(s):

public class Book
    {
        [Identity]
        [Primary]
        public int Id { get; set; }

        [TypeMap(System.Data.DbType.String)]
        public string Name { get; set; }

        [TypeMap(System.Data.DbType.Binary)]
        public byte[] Images { get; set; }

        [TypeMap(System.Data.DbType.String)]
        public Author Author { get; set; }

        [Map("Aid")]
        [TypeMap(System.Data.DbType.Int32)]
        public int Aid { get; set; }
    }

public class Author
    {
        [Identity]
        [Map("Id")]
        [Primary]
        public int Id { get; set; }

        [TypeMap(System.Data.DbType.String)]
        public string Name { get; set; }

        public List<Book> Books { get; set; }
    }

following lines are method used in a class, this class inherits from blow class:

BaseRepository<Book, SqlConnection>

and following methods are working like join not really joins in sql.

public virtual void SelectWithJoin()
        {
            Expression<Func<Author, bool>> y = null;

            Expression<Func<Book, bool>> books = null;

            var x = Query(books).Join(ConnectionToUse.Query<Author>(y), a => a.Aid, a => a.Id, (b, a) =>
            {
                var re = b;

                re.Author = a;

                return re;
            });
        }

        
        public virtual void SelectWithJoin2()
        {
            Expression<Func<Author, bool>> y = null;

            Expression<Func<Book, bool>> books = null;

            var x = Query(books).Join(ConnectionToUse.Query<Author>(y), a => a.Aid, a => a.Id, (b, a) => new Book
            {
                Aid = b.Aid,
                Author = a,
                Id = b.Id,
                Images = b.Images,
                Name = b.Name
            });
        }

[mikependon]

Thank you for your commendation in the first place.

In anyway, the queries you made in your last comment seems to be not performant when compared to QueryMultiple query I recommended. You actually made 2 actual Query calls there, and that has opened the connection twice. By doing that via QueryMultiple, your codes seems to be more readable and you only issued single call (connection open) to multiple execution.

PS: I would assume that what you are looking for is really a "JOIN (ie: INNER JOIN, LEFT JOIN, RIGHT JOIN)" issued at the DB Server itself (and not just on the fly).

This "JOIN" feature is "purposely" not being supported up until the current version. Yes, it is in purpose :) - You seems to be looking the "Include" features of EF Core which was previously supported by RepoDb via Query (recursive: true, depth: 15**), which I had removed.

There is already a talk to re-enable this feature through this issue #263 .

Anyway, it is fair that you would like this feature and I am planning to add the JoinQuery<T1, T2, TResult> feature soon along with QueryRecursive.

For now, since the feature is not available, would you be able to use QueryMultiple or the one you had written until to the fact the desired feature is implemented?

[myabdolmaleki]

Thank you for your commendation in the first place.

In anyway, the queries you made in your last comment seems to be not performant when compared to QueryMultiple query I recommended. You actually made 2 actual Query calls there, and that has opened the connection twice. By doing that via QueryMultiple, your codes seems to be more readable and you only issued single call (connection open) to multiple execution.

PS: I would assume that what you are looking for is really a "JOIN (ie: INNER JOIN, LEFT JOIN, RIGHT JOIN)" issued at the DB Server itself (and not just on the fly).

This "JOIN" feature is "purposely" not being supported up until the current version. Yes, it is in purpose :) - You seems to be looking the "Include" features of EF Core which was previously supported by RepoDb via Query (recursive: true, depth: 15**), which I had removed.

There is already a talk to re-enable this feature through this issue #263 .

Anyway, it is fair that you would like this feature and I am planning to add the JoinQuery<T1, T2, TResult> feature soon along with QueryRecursive.

For now, since the feature is not available, would you be able to use QueryMultiple or the one you had written until to the fact the desired feature is implemented?

hello dear mike
thank a lot, im doing that with my code and im waiting for JoinQuery<T1, T2, TResult>.
i have talked with my friends to migrate from LLBLGEN and EF to your awesome micro orm.
i have a demo of RepoDb in our Organization.
Im waiting for join support.

i hope you are doing greate!

[mikependon]

Thank you for your interest in RepoDb. We are glad that you're helping us on our journey by sharing it to your colleagues.

Please be reminded that your inputs are valuable to us and that it will be considered soon. However, we have a lined-up things-to-do. See below.

• Full Documentation
• PostgreSql Full Support

Right after the mentioned items above, I as an author will make sure that recurive: true or JoinQuery will be prioritized.

I will keep this ticket open until the feature is delivered.

[mikependon]

Honestly, I really do not like to put JOINs in RepoDb due to the fact that in order to make it more performant, it must be handled via SQL Scripts by the developer. Howerver, my decision has changed as the communities are requesting it. I will listen to all of you.

I am considering this feature and as early as now, I would like to think the actual architecture of the actual implementation.

I would like to gather some inputs from some of you about JOIN support. My intention as an author is to make it much more simpler (maybe not too close with IQueryable for now). But, I prefer to use the Linq.Expression on that actually for compilations.

I am asking some of you to help me analyze the "ways" on how to do JOINs here. Think of it as an approach that you would like.

If you are the developer, what/how would it be? You can share the way of coding you'd like.

FYI: @myabdolmaleki @eMTeTeam @rdagumampan @calumchisholm

Feel free to share to your network as well if they would like to contribute here. Thank you in advance.

[calumchisholm]

I'm not sure yet what the most sensible approach would be for the actual implementation, but JOINs are one of those areas where feature support seems to vary considerably across the different RDBMS platforms. Limiting any implementation to INNER JOINs would probably be a good idea while evaluating different approaches.

• RDBMS feature comparison (old, but possibly still relevant)
• Sqlite support

You might also consider implementing UNION / INTERSECT / EXCEPT as a first step - they should be more straightforward to develop than JOIN, but could provide you some insight into the direction you want to take with this.

[mikependon]

@calumchisholm - thank you for the valuable feedback (as always). I have received a bunch of questions and requests on JOIN operations (approximately 10-15 times). I can see that it is much more practical to prioritize this one.

I would like to make a very simplistic approach of doing JOINs and definitely not taking the same path as EF.

I need a collective response from the community before doing an architectural visits on this. It will affect the core implementation of RepoDb, that is why! :)

[mikependon]

Linking this one #263. FYI: @tola

[fake-fur]

are you considering the syntax or the actual code implementation, or both at this point?

[mikependon]

@fake-fur - thanks for digging into this. On this story, I am trying to ask the community the best way to handle the JOIN operation.

Your inputs to the syntax is very important. If you have your preference, please let me know how would you like it to be.

My intention as an author is to create a RepoDb.Queryable (or RepoDb.JoinQuery) extended library, and on that library hopefully, will contain the collective ideas the community provided on this story.

[myabdolmaleki]

hi mr mike

does repodb supports sql join?

[mikependon]

Hey, it is in purpose that we did not supported the JOIN query (and/or SplitQuery) in RepoDb. We highly suggest to you to use the ExecuteQuery method instead and write your own JOIN SQL itself.

[mbirnios]

Hi Mike! We're reviewing your ORM. About JOINs, at least in our case, it wouldn't be a problem to write our own joins in plain SQL (actually, that would be our preffered solution).

My question is related to one-to-one JOINS, and how to map the results back to multiple objects... similar to what Dapper calls "multiple mapping", with the "splitOn" feature (which we kind of dislike, because we need to use literal strings).

Is there (or will be) support on RepoDB for such kind of queries?

In other words, let's say you need to retrieve an Order record, along with the Customer of each order. Is there a way to retrieve this with RepoDb in one shot?

select o.*, c.* from Order as O inner join Customer as C where c.CustomerId = o.CustomerId

And then, something like:

var order = connection.Query<Order, Customer>(o => o.OrderId == 1234);

Then, the order object would be filled in with the Customer property as well?

Thanks!

[mikependon]

@mbirnios - had you visited the QueryMultiple already?

[mikependon]

You can do it with QueryMultiple, but you have to handle the assignment of the child rows into the parent rows.

FYI: it is executing multiple SQL statements in the DB, it is not a real JOIN. Then, it simply maps the result based on the order of generic types you passed in the QueryMultiple method. Max of 7 generic types; it is a Tuple object.

[cleverguy25]

Please, please support at least 1:1 joins. QueryMultiple makes sense for other associations but not having this for 1:1 is just too painful. If I have reference tables and I want to include those like this:

public class ParentTable
{
    public int Id { get;set;}
    public string Name { get; set; }
    public ReferenceType ReferenceType { get; set; }
}

public class ReferenceType
{
    public int Id { get;set;}
    public string Name { get; set; }
}

Writing complex queries for a simple join seems like something this library would want to join.

[mikependon]

@cleverguy25, would you be able to share your thoughts on how would you like to do this as an extension of IDbConnection?

I am aiming to the actual syntax.

Let us say, you have these classes.

public class Supplier
{
    public int Id { get;set;}
    public string Name { get; set; }
    public IEnumerable<Product> Products { get; set; }
}

public class Product
{
    public int Id { get;set;}
    public string Name { get; set; }
}

Would this work for you?

var address = "New York";
var suppliers = connection.Query<Supplier, Product, Supplier>(supplier => supplier.Address = address).FirstOrDefault();
// Process the 'suppliers' and each of the 'supplier.Products'

[cleverguy25]

I would love to help, either with code or just on requirements. 👍 Btw your example references address which is not part of your model. I am shopping for a new ORM for a new product, but hesitant to pick RepoDB even though I immediately mostly love it. I need 1:1 joins. Please consider basic support for that and solve the harder 1:many, many:many problems in another way.

I have written some homegrown ORMs in the past for previous companies, so have some experience here.

But first, I think that this is jumping again to a 1:many or a many:many relationships, and those are more complicated. I would love to focus on 1:1 for some basic support.

So an updated model:

public class Supplier
{
    public int Id { get;set;}
    public string Name { get; set; }
    public string Address { get; set; }
    public SupplierType SupplierType { get; set; }
    public IEnumerable<Product> Products { get; set; }
}

public class SupplierType
{
    public int Id { get;set;}
    public string Name { get; set; }
}

public class Product
{
    public int Id { get;set;}
    public string Name { get; set; }
}

So if I didn't care about products for time being, I could just do Query, either always as it would be a simple join or a flag to include associations.

var address = "New York";
var suppliers = connection.Query<Supplier>(supplier => supplier.Address = address).FirstOrDefault();

I expect in my FluentMap or Attributes I will need to inform you about the join, and whether it could be null or not (or do you infer that with schema binding?) So you can decide on what JOIN type to use.

For 1:many or many to many, I do not mind just doing QueryMultiple and either hand-coding some SQL, or the way you have it today. I do like Include style syntax but get how problematic that is if it is multiple levels. I think your implementation is better as multiple queries in one batch. I would allow multiple includes but only one level. I say let people do that easy style and if it does not perform they have an outlet with hand-tuned SQL and QueryMultiple.

I am not a fan of the Query<Supplier, Product, Supplier> syntax. Feels redundant, I could see if you can make assumptions and just do Query<Supplier, Product> but again 'Include` feels nicer.

[cleverguy25]

Btw, if we can find a way to direct message, we can sync a time to do a zoom or teams call and talk through this, if it helps.

I want to point out that even if I hand tune the SQL, ExecuteQuery or ExecuteQueryMultiple do not help as I cannot extract from the result set if I got the extra columns via Join. So the only way to do 1:1 is to do dynamic and write a separate mapper to my POCO. That is what TinyORM advocates anyways.

[tola]

For some context. Joint Clause can get complex and it might cause circular dependency in POCO if not handled properly.
For example, table A -> F, B ->F,, B->C, D -> E, E ->A

In one of the projects I worked on, there were 6 tables related to each other.
What I ended up doing when I had many joint tables was writing plain SQL and map them manually. The good thing about plain SQL is efficiency and clear statement. The downside is that you need to map them manually to the objects. Mapping SQL result set to object is not difficult but time-consuming if you have a lot.

Off topic: I asked about similar issue here dotnet/efcore#14525 (comment)

[mikependon]

@cleverguy25 - of course we can. I am also open for a WhatsApp call. A lot in the community are requesting this feature TBH. Can you share me your Teams (account) or WhatsApp (mobile number) so we can plan and schedule a meeting?

EDIT: Please do send it on my email address, can be found on the documentation contact page.

[mikependon]

@tola , that's also my point on my side, that's why JOIN is not supported in this library. Though, I have solve this cyclomatic complexity problem in the early days of RepoDb via QueryRecursive feature, but I tend to remove those feature and exchanged it with QueryMultiple and ExecuteQueryMultiple and leave everything to the developers on how they would implement their own SQL Statements.

[cleverguy25]

@tola I agree, but usually there are some signs of either bad ORM model or bad SQL table design. For hydrating deep hierarchies, you should be writing your own SQL.

I want to simplify for 1:1 relationships that can map to 1:1 JOINS. I rarely see 1:1 that are circular, and if that exists then it could be detected and not generate SQL and handle in mapping. That is what I have done with past ORMs.

But also even if I have to hand-write some SQL, right now I cannot easily map it using RepoDbs mapper. I do not mind writing my SQL, but if hand-write mapping code as well, I can just do that via editor macros and use raw ADO.Net. But I think QueryMultiple and ExecuteQueryMultiple are great for those complex scenarios.

[mikependon]

But also even if I have to hand-write some SQL, right now I cannot easily map it using RepoDbs mapper.

RepoDb mapper is not limited if you are returning the resultsets into single model, specially if that does not have a relationship (like Dapper).

Do you mean, relationship mapping? If not, can you elaborate this with some samples?

[cleverguy25]

It is not a single model though. I know how to do this with custom SQL:

public class Supplier
{
    public int Id { get;set;}
    public string Name { get; set; }
    public string Address { get; set; }
    public int SupplierTypeId { get;set;}
    public string SupplierTypeName { get; set; }
}

But I want this:

public class Supplier
{
    public int Id { get;set;}
    public string Name { get; set; }
    public string Address { get; set; }
    public SupplierType SupplierType { get; set; }
}

public class SupplierType
{
    public int Id { get;set;}
    public string Name { get; set; }
}

My understanding is I would have to do ExecuteQuery to dynamic object, then map that dynamic to my POCO model. If I have to do that I can just write ADO.Net.

[mikependon]

@cleverguy25 , that make sense. The same scenario as above, you shared.

[mbirnios]

Hi @mikependon !

Thanks for your support. I didn't try the QueryMultiple, since I believe that's not what I would need. My use case is exactly the one that @cleverguy25 recently posted, where he was trying to retrieve Supplier along with SupplierType. In other words, a 1:1 relationship, a very simple join. I'm not trying to retrieve and order with a collection of products, I think that's clear on your docs.

The way 1:1 relationships are solved in Dapper (which is what we're using now), is to use their "Multiple Mapping" feature (either automatically if you have "Id"s properties, or via the "Split On" param). Basically, you supply a SQL with your JOINs, and 4 or 5 tables comes into the same row. In the Supplier example:

select * from Supplier s inner join SupplierType st on st.SupplierTypeId = s.supplierTypeId
This would return a table with the following columns:

Id               -> This is from Supplier.
Name         -> This is from Supplier.
Address       -> This is from Supplier.
Id                 -> This is from SupplierType.
Name           -> This is from SupplierType.

Because by convention they use the Id property, they can execute:

var mySupplier = reader.Read<Supplier, SupplierType>();

And that would fullfill your supplier object, with the SupplierType property filled in. I was just looking to do those kind of 1:1 joins with RepoDB as well. Please note, I'm not looking to just copy Dapper, since that's actually not one of my favorite implementations. Here's a link from Medium with an image that clearly shows the approach:

https://medium.com/dapper-net/multiple-mapping-d36c637d14fa

BTW, our approach to ORMs here in the company is to write our own SQL for complex queries, to take the most out of the engine. We're not looking for a complex C# to emulate the JOIN. I believe that's where EF goes out of whack, and there's never going to be a perfect solution. Then, map that to our object to model using Dapper. And then yes, we do persistance with EF, to take advantage of the change tracking features.

[mikependon]

@mbirnios - thanks for the insights. What you and @cleverguy25 is requesting is doable, it is a minor tweak in the compiler. We will support this soon! I have a scheduled call with @cleverguy25 to know more about their use-case, after that, I will share the updates on this User Story and will line-it-up for the development.

[mikependon]

@myabdolmaleki @mbirnios @fake-fur @tola @calumchisholm - hi gents, I have a very productive talks with @cleverguy25 today and we are both planning to introduce the feature in RepoDB in which you would be able to make the 1:1 mapping. We are also looking the 1:Many mapping and/or Many:Many mapping, but the intention is first to support the simple case possible. Please remember that this is the initial support of RepoDB in relation to JOIN features, we are doing it in a different way.

Let us say you have the following models.

public class Teacher
{
	public int Id { get; set; }
	public string Name { get; set; }
}

public class Address
{
	public int Id { get; set; }
	public string ZipCode { get; set; } // some has strings, AZ5800
	public string Street { get; set; }
	public string City { get; set; }
	public string Country { get; set; }
}

public class Student
{
	public int Id { get; set; }
	public string Name { get; set; }
	public Teacher Teacher { get; set; }
}

The model properties will be filled properly if you are to execute the ExecuteQuery below,

using (var connection = new SqlConnection(connectionString))
{
	var sql = @"SELECT S.Id
			, S.Name
			, A.Id AS AddressId
			, A.Street AS AdressStreet
			, A.City AS AddressCity
			, A.Country AS AddressCountry
			, T.Id AS TeacherId
			, T.Name AS TeacherName
		FROM [dbo].[Student] S
		INNER JOIN [dbo].[Address] A ON A.Id = S.AddressId
		LEFT JOIN [dbo].[Teacher] T ON T.Id = S.TeacherId
		WHERE (S.Age >= @Age);";
	var student = connection.ExecuteQuery<Student>(sql, new { Age = 18 });
	// The 'student.Teacher' will automatically be filled with the information
}

Notice the aliases, it should always be prefixed by the table name. So this could also be related to multiple 1:1 relationship, not just Student:Teacher and Student:Address.

On the other hand, if you are to use the fluent approach like Query and BatchQuery, you should do the configuration below.

FluentMapper
	.Entity<Student>()
	.Association<Address>(t => a.Id == s.AddressId, AssociationType.Join);
	.Association<Teacher>(t => t.Id == s.TeacherId, AssociationType.Left);

Or via literal string.

FluentMapper
	.Entity<Student>()
	.Association<Address>(new RelationDefinition("Id", "AddressId"), AssociationType.Join);
	.Association<Teacher>(new RelationDefinition("Id", "TeacherId"), AssociationType.Left);

And by doing the normal Query below, would fill the Student.Teacher information.

using (var connection = new SqlConnection(connectionString))
{
	var student = connection.Query<Student>(10045);
	// The 'student.Teacher' and 'student.Address' will automatically be filled with the information
}

You can as well associate using the attribute Association.

[Association(new RelationDefinition("Id", "AddressId"), AssociationType.Join)]
public Address Address { get; set; }

Or

[Association(new RelationDefinition("Id", "TeacherId"), AssociationType.Left)]
public Teacher Teacher { get; set; }

We hope it is okay and make sense to you guys! The depth of the relationship will only be limited to 2, beyond than that, we will throw an exception.

I hope that is not a complex configuration to you as a user.

[tola]

Good progress. Looking forward for more depth of relationship. I usually have a query or stored procedure with more related tables.

[travbeamo]

@mikependon looks like a nice start, but i agree with @tola more depth would be nice

[mikependon]

@tola @travbeamo , thank you for the replies. Please also try to drag friends, colleagues or somebody from the community if they can share/give us more feedback before we dive-down into this solution. We would like to mediate our solution on all possible cases, but we will not deeply support what the deep-object-graph of EF can. But I will make sure to align it with @cleverguy25 , but we will do it in a partial manner.

[enemers]

@mikependon,

Any updates on the Join feature in POCO? I think this is becoming an urgent thing to be achieved since it will spare the devs lot of complexity and give us better performance.

[stefandevo]

Currently missing this feature to move 100% to RepoDb from Servicestack Ormlite.

[moalamri]

@stefandevo the same here as I'm planning to move from Dapper.
I would suggest to have a look at SmartSql and the way it joins tables to have an idea of how the join syntax is handled. It could look simple as
Query<T>().join<T2>((parent, child) => parent.CategoryID == child.Id), "inner join");

Thank you so much for the great ORM

[tb-mtg]

@moalamri Can you point me to an example that shows how the way SmartSql joins tables?

[moalamri]

@tb-mtg the problem with SmartSql and SqlSugar is the documentation. They are written in Chinese..
SmartSql has some English documents, but they aren't much.
I'm using SqlSugar for now. It is fast and easy to use..
It can be registered as a transit service.
The documents are totally Chinese, but i managed to build a full query of more than 7 joins and even with single table for multiple join.
It also can generate models for Dbfirst approach.
If you want me to convert a query for you then just post the raw query with joins and I'll convert it to Sqlsugar.

[moalamri]

Here's an example in Sqlsugar repository of table joining in POCO.

var newDynamic = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
    JoinType.Left, st.SchoolId == sc.Id,
    JoinType.Left, st.Name == di.String
)) .Select((st,sc,di)=>new { name=st.Name,scid=sc.Id }).ToList();

It says in the variable name that it returns dynamic but in fact it doesn't unless you cast it by adding (dynamic) to the select method as below

var newDynamic = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
    JoinType.Left, st.SchoolId == sc.Id,
    JoinType.Left, st.Name == di.String
)) .Select((st,sc,di)=>new (dynamic){ name=st.Name,scid=sc.Id }).ToList();

this is one of the problems i faced where i had to google translate the documents so i can understand.
And for the respect of Repodb i suggest that we move this conversation to the mentioned repository.

I still use Repodb tho for other non rational queries

[fenilrathod]

Hi @mikependon, are you planning to add Join (simple 1:1) support in RepoDb?

[mikependon]

Yes, we will revisit the architecture and will include this in the near future.

[tehmufifnman]

Glad to hear join support is coming!

[natalie-o-perret]

Hi @mikependon, are you planning to add Join (simple 1:1) support in RepoDb?

Any news regarding 1:1, 1:N, N:M?