40th back to EF architecture ~ One-to-one and one-to-many implementation of left join in LinqToEntity, eflinqtoentity

40th back to EF architecture ~ One-to-one and one-to-many implementation of left join in LinqToEntity, eflinqtoentity

Back to directory

I have already introduced the implementation of left join in linq to SQL. This article is due to a problem recently encountered in the project. It took me a lot of time to solve this problem, there may be two hours. The event is like this. For two tables, they are in a one-to-many relationship, but the requirement is to return a one-to-one relationship and return the latest data, many of you know that you can use inner join. However, for inner join, when processing a one-to-many relationship, multiple records will appear, which is also normal; however, it does not meet our needs today. After testing, we found a solution to this problem. Let's look at the code below:

One-to-multiple relationship

from r in base.GetModel()join data in new TsingDa_NewLearningBarRepository<FAQ_Reply>(UnitWork).GetModel().Where(i => i.Status == (int)Status.Normal)on r.FAQInfoID equals data.FAQInfoID into list

This is easy to understand. The FAQ_Reply set that meets faqinfoId is obtained and put into the list variable.

One-to-one relationship

from r in base.GetModel()join data in new TsingDa_NewLearningBarRepository<FAQ_Reply>(UnitWork).GetModel().Where(i => i.Status == (int)Status.Normal)on r.FAQInfoID equals data.FAQInfoID

This method is a formal inner join method. It is suitable for the one-to-one relationship in the data structure, that is, two tables have the same primary key, and they are strictly one-to-one in the data, if it is not one-to-one, this statement will repeat data!

The following is a one-to-one transformation of left join in EF.

 join data in new TsingDa_NewLearningBarRepository<FAQ_Reply>(UnitWork).GetModel().Where(i => i.Status == (int)Status.Normal) on r.FAQInfoID equals data.FAQInfoID into list from data in list.EmptyOrDefalt()

Unfortunately, it is not successful, but the returned duplicate data, which is acceptable in traditional linq to SQL, but not in linq to entity, because they generate different SQL statement kernels

Correct one-to-one approach

 join data in new TsingDa_NewLearningBarRepository<FAQ_Reply>(UnitWork).GetModel().Where(i => i.Status == (int)Status.Normal) on r.FAQInfoID equals data.FAQInfoID into list select new FAQ_Info_Ext() {  FAQReply = list.OrderByDescending(i => i.AddTime).FirstOrDefault() }

We can see that it is clever in assigning values to data...

Monitor a database

After seeing it, it was really a big jump for me. How did I connect to so many databases? I was dizzy. Then, I set a breakpoint to debug it locally. It turned out to have nothing to do with my code, it's a fault caused by other code!

My code runs like this.

This is acceptable, as I expected.

Back to directory

They use a three-tier architecture in the ASPNET environment. The C # language can implement the following functions: Teaching tables, student tables, and class tables.

Are you looking for someone to do it for you? Don't you know how to start?
If ado.net is not used, use LinQto SQL or Entity Framework.
Ado.net is required, or you can read the example on your own and try again.