What problems can. Net ORM navigation properties solve?

Time:2020-10-1

Write at the beginning

From the earliest single table operations,

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

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

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


Getting ready for the play

FreeSQL is. Net ORM, which can support. Netframework4.0 +,. NETCORE, xamarin, XAUI, blazor, and other indescribable running platforms. Because the code is green and independent, it is very simple to support new platforms. 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. It is gratifying that FreeSQL has joined the NCC open source community: https://github.com/dotnetcore/FreeSql After 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 make wheels over and over again?

What problems can. Net ORM navigation properties solve?

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

  • Support the migration of codefirst contrast structure change;
  • Support dbfirst to import entity classes from database;
  • Support rich expression functions and user-defined parsing;
  • Support batch addition, batch update and bulkcopy;
  • Support navigation attributes, greedy loading, delayed loading, cascade saving;
  • Support read-write separation, separate tables and databases, and tenant design;
  • Support MySQL / sqlserver / PostgreSQL / Oracle / SQLite / Damon / Shentong / Renmin Jincang / 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

Manytoone many to one

From the perspective of table 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 tables. 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 Returned together, a type can correspond to n topics. For topics, it is n to 1, so I named it manytoone

When entity query is used in C ා, it is easy for n to query 1 scene, but it is inconvenient to receive objects, 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 create a topicdto. However, there are too many query scenarios to enumerate topicdto. As the requirements change, the latter dto will become more and more rampant.

What problems can. Net ORM navigation properties solve?

Therefore, intelligent human thought of navigation attribute, and added type attribute in topic entity to receive returned data.

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

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

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

What problems can. Net ORM navigation properties solve?

After one evolution, we configured the join conditions

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 (NAMEOF (typeID))] means, Topic.typeid And Type.id It is omitted here Type.id Because Type.id It is the primary key (known conditions need not be configured), so as to simplify 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 a topic, all navigation properties are returned each time?

Thus: to list, only the n-to-1 navigation attribute fields that have been included or used will be returned.

  • 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 typeYou 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. For example, such queries:

fsql.Select <Tag>().Where(a => a. Parent.Parent.name  =="Cantonese"). Tolist();
//This 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?


Onetoone one on one

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

Like order, order_ Detail two tables, one-to-one scenario:

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.

For one-to-one, the target entity attribute must exist on both sides, and the primary key should be used for navigation 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 problems can. Net ORM navigation properties solve?

Onetomany one to many

1 to N, and N to 1 is the opposite

Topic is n to 1 relative to type

Type is a pair of N relative to topic

What problems can. Net ORM navigation properties solve?

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

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

1. Main advantages of N navigation attributes:

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

Onetomany cascaded 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 is query from the type direction, which is in line with the user’s data format requirements.

Finally, the data is returned by SQL query twice, which is roughly as follows:

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

Method 2: from the topic direction, you can also find:

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

A SQL query returns all the data, approximately:

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

What problems can. Net ORM navigation properties solve?

Explanation: Although method 1 includes many queries twice, its IO performance is much better than that of method 2. Method 2 query simple data is OK, more complex, it is easy to generate a large number of duplicate IO data. Moreover, 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 records to the corresponding C ා Java PHP Type.Topics Medium?

So at this time, configure the navigation relationship.

N to 1, such configuration (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 for N, this configuration (find the field from the target type and associate it with its 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, we can also include many (a = > A. topics, then = > a then.IncludeMany (b => b.Comments))

Suppose that you also need to query the comments corresponding to the topic. Up to three SQL queries will be generated:

select * from type where name in ('c#', 'java', 'php')
Select * from topic where typeID in
Select * from comment where topic in (ID returned by previous SQL)

Thinking: This cascading query has some disadvantages. For example, there are 1000 articles below C ා, but are they all returned?

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

In this way, we can solve the problem that only 10 pieces of data are returned for each category. The EF / efcore function is not available until efcore 5.0. This may be one of the reasons why many people avoid EF navigation attributes. A few months ago, I tested efcore 5.0 SQLite, which reported an error. Maybe it only supports SQL server. However, FreeSQL has no database type restrictions, but it still says: it’s all sons!

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

What problems can. Net ORM navigation properties solve?

Onetomany cascade save

In practice, n-to-1 is not suitable for cascade preservation. Save the type information when saving a topic? I personally think the bottom-up saving function is too uncontrollable. At present, FreeSQL does not support bottom-up saving.

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

Cascade storage 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 the type. If it is self increment, get the self increment, assign it to topics downward, and then insert the 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; }
}

Look, it’s complicated?? After seeing how simple the query is, it’s really worth everything!

The main advantages of n to n navigation attributes are as follows:

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

Manytomany cascaded 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 returned by SQL query twice, which is roughly as follows:

select * from tag where name in ('c#', 'java', 'php')
Select * from topic where id in (select topic from topictag where TagID in (ID returned from the previous SQL)

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

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

You can also check it in reverse Topic.Type.name Query the topics of C ා Java PHP and their tags

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]
}
...
]

For N-to-N cascaded queries, just like 1-to-N, they all use include many. N-to-N include many can also be continued 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#' ) 
)

Manytomany cascade preservation

Cascade storage 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: "FreeSQL 1.8.1 officially released",
  Tags = new List<Tag>(new[] {
    new Tag { name = "c#" }
  })
});

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

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

Other methods provided repo.SaveMany (topic entity, “tags”) saves the topictag data completely. For example, when the. Tags property of topic entity is empty, delete all table data of topic entity existing in topic tag.

Savemany mechanism: complete save, compare the existing data of the topictag table, and calculate the add, modify and delete execution.

What problems can. Net ORM navigation properties solve?

Father son relationship

Father child relationship is actually a complex of manytoone and onetomany, which points to itself and is often used in tree structure table design.

In addition to using manytoone and onetomany, parent-child relationship also provides CTE recursive query and memory recursive data assembly functions.

What problems 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 you have configured the parent-child attribute, you can use this method:

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);

Since the query data is flat, the totreelist method processes the returned flat data into a tree list in memory.


CTE recursive deletion

It is a very common function of infinite level classification table. When deleting a tree node, the child node is also processed.

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

If soft delete:

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

CTE recursive query

If there is no design of infinite level classification table with data redundancy, recursive query is indispensable. Astreect is the encapsulation of recursive query

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

Tested databases: MySQL 8.0, sqlserver, PostgreSQL, Oracle, SQLite, Damon, Renmin Treasury

Pose 1: astreete() + totreelist

var t2 = fsql.Select<Area>()
  . where (a = > a.name = = China)
  . astreecte() // query all records in China
  .OrderBy(a => a.Code)
  . totreelist(); // you can use tolist if you don't have to
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: astreecite() + 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 pose one

Pose 3: astreete (pathselector) + tolist

How do I return hidden fields after setting the 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 problems can. Net ORM navigation properties solve?

summary

Microsoft has produced 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 pair of n) provides simple and controllable cascading query and cascade saving functions;
  • Manytomany (many to many) provides simple many to many filter query, cascade query, cascade save function;
  • The parent-child relationship provides common CTE query, deletion and recursion functions;

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

Free SQL open source protocol MIT https://github.com/dotnetcore/FreeSql , can be commercially available, complete documentation. QQ group: 4336577 (full), 8578575 (online), 52508226 (online)

If you have good ORM implementation idea, welcome to leave a message to the author for discussion, thank you for watching!

Recommended Today

Memento code examples of PHP design patterns (25)

objective It provides recovery to the previous state (using rollback) or getting the internal state of the object without breaking the encapsulation (the object does not need to have a function that returns the current state). The memo pattern is implemented using three classes: originator, caretaker, and memento. Memento — responsible for storing the unique […]