Entity Framework Multi-query and multiple notation

Using EF to implement multiple queries without considering performance issues

October 6, 2015 22:26:51

===== Body =====

I have three tables in the database, such as:

Sysmenus and Sysfunction have a primary foreign key relationship, while SysUserInfo is not associated with the other two tables.

I now use these three tables to implement multiple query methods using EF for a variety of situations

Method One: Include method (or EF's own navigation property)

Query, is to be out of data. So I'll start with the EF's own navigation properties, because that's a common method.

The navigation properties I'm talking about are:

Shown below the model table, you can see that only tables that have a primary foreign key association have navigation properties.

It is also very easy to use, on the code

It's not easy to just point out the table.

Using the Include method is:

In addition to one more include method, the other is consistent.

The difference is that SQL statement generation differs by using the SQL Instrumentation tool to know

SELECT TOP (1) [Extent1]. [FID] As [FID], [Extent1]. [Mid] as [mid], [Extent1]. [FName] As [FName], [Extent1]. [Ffunction] As [Ffunction], [Extent1]. [Fpicname] As [Fpicname], [Extent1]. [Fstatus] As [Fstatus], [Extent1]. [Fcreatorid] As [Fcreatorid], [Extent1]. [Fcreatetime] As [Fcreatetime], [Extent1]. [Fupdateid] As [Fupdateid], [Extent1]. [Fupdatetime] As [Fupdatetime], [Extent2]. [MID] as [mID1], [Extent2]. [Mparentid] As [Mparentid], [Extent2]. [Mname] As [Mname], [Extent2]. [Murl] As [Murl], [Extent2]. [MArea] As [MArea], [Extent2]. [Mcontroller] As [Mcontroller], [Extent2]. [Maction] As [Maction], [Extent2]. [Msortid] As [Msortid], [Extent2]. [Mstatus] As [Mstatus], [Extent2]. [Mpicname] As [Mpicname], [Extent2]. [Mlevel] As [Mlevel], [Extent2]. [MEXP1] As [MEXP1], [Extent2]. [MEXP2] As [MEXP2], [Extent2]. [Mcreatorid] As [Mcreatorid], [Extent2]. [Mcreatetime] As [Mcreatetime], [Extent2]. [Mupdateid]As [Mupdateid], [Extent2]. [Mupdatetime] As [mupdatetime] from [dbo]. [Sysfunction] As [Extent1] INNER JOIN [dbo]. [Sysmenus] As [Extent2] on [Extent1]. [MID] = [Extent2]. [MID]

The Include method inner the join table for all the data, and the direct use of the navigation property is just a single-table query (no sysmenus table how with Mname?). Question point)

So I'm still using the include

But a table model without a primary foreign key relationship does not have a navigation property, so include cannot use a table that does not have a primary foreign key relationship between the tables!

Method Two: Use the Join method

There is a primary foreign key relationship

The generated SQL statement

SELECT     [extent1].[ Mid] as [mid],     [extent1].[ Mname] As [Mname],     [extent2].[ FName] as [FName]    from  [dbo].[ Sysmenus] as [Extent1]    INNER JOIN [dbo].[ Sysfunction] as [Extent2] on [Extent1]. [MID] = [Extent2]. [MID]

No primary foreign key relationship

SQL statements

SELECT     [extent1].[ Mid] as [mid],     [extent1].[ Mname] As [Mname],     [extent2].[ Uloginname] as [Uloginname]    from  [dbo].[ Sysmenus] as [Extent1]    INNER JOIN [dbo].[ SysUserInfo] as [Extent2] on [Extent1]. [MID] = [Extent2]. [UID]

The resulting SQL statement shows that joins can also be perfectly linked, regardless of whether the two tables have a primary foreign key association. But the join method has one drawback, the inability to use multiple join tables, how do you want to connect three tables? A: Join can't be done.

Method Three: Use the class SQL statement (how to read?) ）

SELECT TOP (1)     [extent1].[ Mid] as [mid],     [extent1].[ Mparentid] As [Mparentid],     [extent1].[ Mname] As [Mname],     [extent1].[ Murl] As [Murl],     [extent1].[ MArea] As [MArea],     [extent1].[ Mcontroller] As [Mcontroller],     [extent1].[ Maction] As [maction],     [extent1].[ Msortid] As [Msortid],     [extent1].[ Mstatus] As [Mstatus],     [extent1].[ Mpicname] As [Mpicname],     [extent1].[ Mlevel] As [Mlevel],     [extent1].[ MEXP1] As [MEXP1],     [extent1].[ MEXP2] As [MEXP2],     [extent1].[ Mcreatorid] As [Mcreatorid],     [extent1].[ Mcreatetime] As [mcreatetime],     [extent1].[ Mupdateid] As [Mupdateid],     [extent1].[ Mupdatetime] as [Mupdatetime]    from   [dbo].[ Sysmenus] as [Extent1]    INNER JOIN [dbo].[ Sysfunction] as [Extent2] on [Extent1]. [MID] = [Extent2]. [MID]    INNER JOIN [dbo]. [SysUserInfo] As [Extent3] on [Extent1]. [MID] = [Extent3]. [UID]

It's like writing SQL statements, but I feel the same as include, because there are no navigation properties, and it's not going to work.

If there is any mistake, I hope you will be able to point out the supervision.

Entity Framework Multi-query and multiple notation