What can. Net ORM navigation properties solve?

Time:2021-4-18

Write at the beginning

From the earliest single table operation,

Later, I contacted left join, right join and inner join queries,

Because of the limited funds, we need to deal with the actual needs in multi table query. Do you have such experience?

This paper explains the design idea of navigation attributes (manytoone, onetomany, manytomany) from the actual development requirements, and what problems have been solved. Tip: the following example code uses FreeSQL syntax, and some pseudo code.


Preparation for entering the play

FreeSQL is. Net ORM, which can support. Netframework 4.0 +,. NETCORE, xamarin, XAUI, blazor, and other running platforms. Because the code is green and independent, it is very simple to support the new platform. At present, the number of unit tests is 5000 +, the number of nuget downloads is 180K +, and the source code is submitted almost every day. We are glad that FreeSQL has joined the NCC open source communityhttps://github.com/dotnetcore/FreeSqlAfter joining the organization, the community has a greater sense of responsibility and needs to work harder to improve the quality and contribute to the open source community.

QQ group: 4336577 (full), 8578575 (online), 52508226 (online)

Why repeat the wheel?

What can. Net ORM navigation properties solve?

The main advantage of FreeSQL lies in its ease of use. It is basically out of the box and has good compatibility in switching between different databases. The author has spent a lot of time and energy on this project, please spend half an hour to understand the project, thank you. The functional features are as follows:

  • Support the migration of codefirst comparing with structural changes;
  • Support dbfirst to import entity class from database;
  • Support rich expression function, custom parsing;
  • Support batch addition, batch update and bulkcopy;
  • Support navigation attribute, greedy loading, delay loading and cascade saving;
  • It supports the separation of reading and writing, table and database, and tenant design;
  • Support MySQL / sqlserver / PostgreSQL / Oracle / SQLite / Dameng / Shentong / dajincang / msaccess;

FreeSQL is very simple to use. You only need to define an ifreesql object

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    . useautosyncstructure (true) // automatically synchronize entity structure to database
    . build(); // be sure to define singleton singleton singleton mode

Manytoone many to one

From the perspective of foreign keys, left join, right join and inner join are mainly for one-to-one and many to one queries, such as topic and type. A topic can only belong to one type

select
topic.*, type.name
from topic
inner join type on type.id = topic.typeid

Query topic type.name Let’s go back together. A type can correspond to n topics. For a topic, it’s n-to-1, so I name it manytoone

When using entity query in C #, n-to-1 scene query is easy, but receiving object is not convenient, as follows:

fsql.Select<Topic, Type>()
  .LeftJoin((a,b) => a.typeid == b.Id)
  .ToList((a,b) => new { a, b })

In this way, only anonymous types can be returned, unless you build a topicdto yourself. However, there are too many query scenarios, and it is almost impossible to enumerate topicdto. With the change of requirements, the latter dto will become more and more popular.

What can. Net ORM navigation properties solve?

So the intelligent human thought of the navigation attribute, adding the type attribute in the topic entity to receive the returned data.

fsql.Select<Topic>()
   .LeftJoin((a,b) => a.Type.id == a.typeid)
   .ToList();

After returning the data, you can use [0] Type.name Get the classification name.

After a period of use, I found that innerjoin’s condition was always written repeatedly, and I had to use my brain to recall this condition every time (on how my hair fell off).

What can. Net ORM navigation properties solve?

After one evolution, we configure the condition of join

class Topic
{
    public int typeid { get; set; }
    [Navigate(nameof(typeid))]
    public Type Type { get; set; }
}
class Type
{
    public int id { get; set; }
    public string name { get; set; }
}

When querying, it becomes like this:

fsql.Select<Topic>()
   .Include(a => a.Type)
   .ToList();

After returning data, you can also use [0] Type.name Get the classification name.

  • [navigate (name of (typeID))] means, Topic.typeid And Type.id Correlation, omitted here Type.id Because Type.id Is the primary key (known conditions do not need to configure), so as to achieve the effect of simplifying the configuration
  • . include (a = > a.type) will be automatically converted to. Leftjoin (a = > a Type.id == a.typeid)

Thinking: tolist returns topic by defaultAnd typeNo, because when there are many navigation properties under topic, all navigation properties are returned every time?

Therefore: only included or used n-to-1 navigation attribute fields will be returned in tolist.

  • fsql.Select < topic > (). Tolist(); returns topic*
  • fsql.Select < topic > (). Include (a = > a.type). Tolist(); returns topicAnd type
  • fsql.Select <Topic>().Where(a => a. Type.name ==”C #). Tolist(); returns topicAnd type, you do not need to explicitly use include (a = > a.type)
  • fsql.Select <Topic>().ToList(a => new { Topic = a, TypeName = a. Type.name }); returns topic. * and type.name

With these mechanisms, all kinds of complex n-to-1 queries are very good, such as this query:

fsql.Select <Tag>().Where(a => a. Parent.Parent.name  =="Cantonese"). Tolist ();
//The code generates three tag table left join queries.

class Tag {
  public int id { get; set; }
  public string name { get; set; }
  
  public int? parentid { get; set; }
  public Tag Parent { get; set; }
}

Is it more convenient than using left join / inner join / right join?


One to one

The purpose of one-to-one and n-to-1 solutions is the same, both of which are to simplify multi table join queries.

Like order, order_ Two tables, one-to-one scene:

fsql.Select<order>().Include(a => a.detail).ToList();

fsql.Select<order_detail>().Include(a => a.order).ToList();

The query data is the same, but the returned C # type is different.

One to one, but the configuration is a little different. The usage is the same as n to 1.

One to one, it requires that there are target entity attributes on both sides, and primary keys are used to navigate on both sides.

class order
{
    public int id { get; set; }
    [Navigate(nameof(id))]
    public order_detail detail { get; set; }
}
class order_detail
{
    public int orderid { get; set; }
    [Navigate(nameof(orderid))]
    public order order { get; set; }
}

What can. Net ORM navigation properties solve?

One to many

1 to N, and N to 1 is the opposite

Topic is n to 1 relative to type

Type is one pair of N relative to topic

What can. Net ORM navigation properties solve?

Therefore, we can define the list < topic > topics {get; set;} navigation attribute in the type entity class

class Type
{
    public int id { get; set; }
    public List<Topic> Topics { get; set; }
}

1. Main advantages of n-pair navigation attributes:

  • When you query type, you can query topic together, and still use type as the return type.
  • When adding type, add topics together
  • When updating type, update topics together
  • When deleting type, there is no action (EF uses the database foreign key function to delete sub table records)

Onetomony cascade query

Put Type.name Query for C # Java PHP and their topics:

Method 1

fsql.Select<Type>()
   .IncludeMany(a => a.Topics)
   .Where(a => new { "c#", "java", "php" }.Contains(a.name))
   .ToList();
[
{
  name : "c#",
  Topics: [article list]
}
...
]

This method queries from the type direction, which is very consistent with the user’s data format requirements.

Finally, the data is queried by SQL twice, which is about:

select * from type where name in ('c#', 'java', 'php')
Select * from topics where typeID in

Method 2: it can also be found from the topic direction

fsql.Select<Topic>()
   .Where(a => new { "c#", "java", "php" }.Contains(a.Type.name)
   .ToList();

A SQL query returns all the data, which is roughly as follows:

select * from topic
left join type on type.id = topic.typeid
where type.name in ('c#', 'java', 'php')

What can. Net ORM navigation properties solve?

Explanation: Although method 1 includes many queries twice separately, the IO performance is much better than method 2. Method 2: it’s OK to query simple data, and it’s easy to generate a large number of duplicate IO data if it’s a little more complex. And the data structure list < topic > returned by method 2 generally does not meet the user’s requirements.

When includemany queries topic for the second time, how to allocate the records to the corresponding fields of C # Java PHP Type.Topics Chinese?

So at this time, just configure the navigation relationship.

N to 1, which is configured in this way (find a field from yourself and associate it with the primary key of the target type)

class Topic
{
    public int typeid { get; set; }
    [Navigate(nameof(typeid))]
    public Type Type { get; set; }
}

1 pair of N, configured in this way (find the field from the target type and associate it with your own primary key)

class Type
{
    public int id { get; set; }
    [Navigate(nameof(Topic.typeid))]
    public List<Topic> Topics { get; set; }
}

infer other things from one fact:

In the actual development, it can also include many (a = > A. topics, then = > a then.IncludeMany (b => b.Comments))

Suppose you also need to find out the comments corresponding to topic. Up to three SQL queries are generated:

select * from type where name in ('c#', 'java', 'php')
Select * from topic where typeID in
Select * from comment where topic in

Thinking: This cascading query actually has disadvantages. For example, there are 1000 articles in C # below, and they are all returned?

IncludeMany(a => a.Topics.Take(10))

In this way, only 10 pieces of data can be returned for each classification. EF / efcore can’t do this function until efcore 5.0. This may be one of the reasons why many people taboo EF navigation properties. A few months ago, I tested efcore 5.0 SQLite, which reports an error. Maybe it only supports SQL server. And FreeSQL has no database type limit, or that sentence: all are sons!

For more about includemany, please refer to the GitHub wiki documentation.

What can. Net ORM navigation properties solve?

Onetomony cascade save

In practice, it is found that n-to-1 is not suitable for cascade storage. When saving a topic, save the type information as well? I personally think that the bottom-up save function is too uncontrollable. FreeSQL does not support bottom-up save at present.

FreeSQL supports cascading save from top to bottom. For example, when you save a type, you can also save its topic.

Cascade save is recommended for less important functions or test data addition

var repo = fsql.GetRepository<Type>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Type
{
  name = "c#",
  Topics = new List<Topic>(new[] {
    new Topic
    {
        ...
    }
  })
});

First, add type. If it is self increasing, get self increasing, assign it to topics, and then insert topic.


Many to many

Many to many is a very common design, such as: topic, tag, topictag

class Topic
{
    public int id { get; set; }
    public string title { get; set; }

    [Navigate(ManyToMany = typeof(TopicTag))]
    public List<Tag> Tags { get; set; }
}
public Tag
{
    public int id { get; set; }
    public string name { get; set; }

    [Navigate(ManyToMany = typeof(TopicTag))]
    public List<Topic> Topics { get; set; }
}
public TopicTag
{
    public int topicid { get; set; }
    public int tagid { get; set; }

    [Navigate(nameof(topicid))]
    public Topic Topic { get; set; }
    [Navigate(nameof(tagid))]
    public Tag Tag { get; set; }
}

Looking at it, I feel complicated?? After looking at how simple the query is, it’s really worth everything!

Main advantages of N-to-N navigation attributes:

  • When querying topic, you can query tag together and use topic as the return type.
  • When adding a topic, add tags together
  • When updating topics, update tags together
  • When deleting a topic, there is no action (EF uses the database foreign key function to delete sub table records)

Manytomany cascade query

Put Tag.name Query for C # Java PHP and their topics:

fsql.Select<Tag>()
   .IncludeMany(a => a.Topics)
   .Where(a => new { "c#", "java", "php" }.Contains(a.name))
   .ToList();
[
{
  name : "c#",
  Topics: [article list]
}
...
]

Finally, the data is queried by SQL twice, which is about:

select * from tag where name in ('c#', 'java', 'php')
Select * from topic where id in (select topic from topic where tag in)

If Tag.name =There are too many topic records under “C #”, just want to return to top 10:

.IncludeMany(a => a.Topics.Take(10))

You can also look it up the other way around Topic.Type.name Query the topic of C # Java PHP and their tag:

fsql.Select<Topic>()
   .IncludeMany(a => a.Tags)
   .Where(a => new { "c#", "java", "php" }.Contains(a.Type.name))
   .ToList();
[
{
  Title: "FreeSQL 1.8.1 officially released",
  Type: { name: "c#" }
  Tags: [tag list]
}
...
]

N-to-N cascading queries, like 1-to-N, use includemany, and N-to-N includemany can continue down then.

Inquiry Tag.name =All topics of “C”

fsql.Select<Topic>()
   .Where(a => a.Tags.AsSelect().Any(b => b.name = "c#"))
   .ToList();

The generated SQL is like this:

select * from topic
where id in ( 
    select topicid from topictag 
    where tagid in ( select id from tag where name = 'c#' ) 
)

Many to many cascade save

Cascade save is recommended for less important functions or test data addition

var repo = fsql.GetRepository<Topic>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Topic
{
  Title = "official release of FreeSQL 1.8.1,",
  Tags = new List<Tag>(new[] {
    new Tag { name = "c#" }
  })
});

Insert topic, and then judge whether tag exists (if not, insert tag).

Get topic.id And tag.id Then insert the topictag.

Another method is provided repo.SaveMany (topic entity, “tags”) completely saves the topictag data. For example, when the. Tags attribute of the topic entity is empty, the deletion of the topic entity exists in all table data of the topic tag.

Savemany mechanism: save completely, compare the existing data in the topictag table, and calculate the execution of adding, modifying, and deleting.

What can. Net ORM navigation properties solve?

Father son relationship

Father son relationship is actually a complex of many to one and one to many. It points to itself and is often used in tree structure table design.

In addition to using the methods of many to one and one to many, the parent-child relationship also provides the functions of CTE recursive query and memory recursive assembly data.

What can. Net ORM navigation properties solve?

public class Area
{
  [Column(IsPrimary = true)]
  public string Code { get; set; }

  public string Name { get; set; }
  public string ParentCode { get; set; }

  [Navigate(nameof(ParentCode))]
  public Area Parent { get; set; }
  [Navigate(nameof(ParentCode))]
  public List<Area> Childs { get; set; }
}

var repo = fsql.GetRepository<Area>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Area
{
  Code = "100000",
  Name = China,
  Childs = new List<Area>(new[] {
    new Area
    {
      Code = "110000",
      Name = Beijing,
      Childs = new List<Area>(new[] {
        New area {code = 110100 ", name = Beijing},
        New area {code = 110101 ", name = Dongcheng District},
      })
    }
  })
});

Recursive data

After configuring the parent-child property, you can use it like this:

var t1 = fsql.Select<Area>().ToTreeList();
Assert.Single(t1);
Assert.Equal("100000", t1[0].Code);
Assert.Single(t1[0].Childs);
Assert.Equal("110000", t1[0].Childs[0].Code);
Assert.Equal(2, t1[0].Childs[0].Childs.Count);
Assert.Equal("110100", t1[0].Childs[0].Childs[0].Code);
Assert.Equal("110101", t1[0].Childs[0].Childs[1].Code);

The query data is originally planar, and the totreelist method processes the returned planar data into a tree list in memory.


CTE recursive deletion

It’s a very common function of infinite level classification table. When you delete a tree node, you should also deal with the child node.

fsql.Select<Area>()
  . where (a = > a.name = = China)
  .AsTreeCte()
  .ToDelete()
  . executeafrows(); // delete all records in China

If soft delete:

fsql.Select<Area>()
  . where (a = > a.name = = China)
  .AsTreeCte()
  .ToUpdate()
  .Set(a => a.IsDeleted, true)
  . executeafrows(); // soft delete all records in China

CTE recursive query

If you don’t design infinite level classification table with data redundancy, recursive query is indispensable. Astreecite is the encapsulation to solve recursive query

parameter describe
(optional) pathselector Path content selection, you can set the query return: China, Beijing, Dongcheng District
(optional) up False (default): recursive query from parent to child, true: recursive query from child to parent
(optional) PathSeparator Set the connector of pathselector, default: – >
(optional) level Set recursion level

The tested databases: MySQL 8.0, sqlserver, PostgreSQL, Oracle, SQLite, Dameng, Da Da Jin Cang

Pose 1: astreecte() + totreelist

var t2 = fsql.Select<Area>()
  . where (a = > a.name = = China)
  . astreecite() // query all records in China
  .OrderBy(a => a.Code)
  . totreelist(); // you can also use tolist if you don't have to (see pose 2)
Assert.Single(t2);
Assert.Equal("100000", t2[0].Code);
Assert.Single(t2[0].Childs);
Assert.Equal("110000", t2[0].Childs[0].Code);
Assert.Equal(2, t2[0].Childs[0].Childs.Count);
Assert.Equal("110100", t2[0].Childs[0].Childs[0].Code);
Assert.Equal("110101", t2[0].Childs[0].Childs[1].Code);
// WITH "as_tree_cte"
// as
// (
// SELECT 0 as cte_level, a."Code", a."Name", a."ParentCode" 
// FROM "Area" a 
//Where (A. "name" ='China ')

// union all

// SELECT wct1.cte_level + 1 as cte_level, wct2."Code", wct2."Name", wct2."ParentCode" 
// FROM "as_tree_cte" wct1 
// INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
// )
// SELECT a."Code", a."Name", a."ParentCode" 
// FROM "as_tree_cte" a 
// ORDER BY a."Code"

Pose 2: astreecte () + tolist

var t3 = fsql.Select<Area>()
  . where (a = > a.name = = China)
  .AsTreeCte()
  .OrderBy(a => a.Code)
  .ToList();
Assert.Equal(4, t3.Count);
Assert.Equal("100000", t3[0].Code);
Assert.Equal("110000", t3[1].Code);
Assert.Equal("110100", t3[2].Code);
Assert.Equal("110101", t3[3].Code);
//The SQL executed is the same as the first one

Posture 3: astreecte (pathselector) + tolist

How to return hidden fields after setting pathselector parameter?

var t4 = fsql.Select<Area>()
  . where (a = > a.name = = China)
  .AsTreeCte(a => a.Name + "[" + a.Code + "]")
  .OrderBy(a => a.Code)
  .ToList(a => new { 
    item = a, 
    level = Convert.ToInt32("a.cte_level"), 
    path = "a.cte_path" 
  });
Assert.Equal(4, t4.Count);
Assert.Equal("100000", t4[0].item.Code);
Assert.Equal("110000", t4[1].item.Code);
Assert.Equal("110100", t4[2].item.Code);
Assert.Equal("110101", t4[3].item.Code);
Assert.Equal (China [100000], T4 [0]. Path);
Assert.Equal (China [100000] - > Beijing [110000], T4 [1]. Path);
Assert.Equal (China [100000] - > Beijing [110000] - > Beijing [110100], T4 [2]. Path);
Assert.Equal (China [100000] - > Beijing [110000] - > Dongcheng District [110101], T4 [3]. Path);
// WITH "as_tree_cte"
// as
// (
// SELECT 0 as cte_level, a."Name" || '[' || a."Code" || ']' as cte_path, a."Code", a."Name", a."ParentCode" 
// FROM "Area" a 
//Where (A. "name" ='China ')

// union all

// SELECT wct1.cte_level + 1 as cte_level, wct1.cte_path || ' -> ' || wct2."Name" || '[' || wct2."Code" || ']' as cte_path, wct2."Code", wct2."Name", wct2."ParentCode" 
// FROM "as_tree_cte" wct1 
// INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
// )
// SELECT a."Code" as1, a."Name" as2, a."ParentCode" as5, a.cte_level as6, a.cte_path as7 
// FROM "as_tree_cte" a 
// ORDER BY a."Code"

What can. Net ORM navigation properties solve?

summary

Microsoft has made an excellent language C #. It can do some very useful functions by using the language features. It is very suitable to use navigation properties in orm.

  • Manytoone (n to 1) provides a simple multi table join query;
  • Onetomany (1 to n) provides simple and controllable cascade query and cascade save functions;
  • Manytomany (many to many) provides simple many to many filtering query, cascade query and cascade save functions;
  • The parent-child relationship provides common functions of CTE query, deletion and recursion;

I hope you can move your little finger and forward the article to let more people know that. Net has such a good orm. Thank you!!

FreeSQL open source protocol MIThttps://github.com/dotnetcore/FreeSql, which can be used for commercial use with complete documents. QQ group: 4336577 (full), 8578575 (online), 52508226 (online)

If you have a good idea of ORM implementation, please leave a message for the author to discuss. Thank you for watching!