Shulou(Shulou.com)06/02 Report--

This article mainly shows you "EF Code First how to achieve data query", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "EF Code First how to achieve data query" this article.

I. Preface

EF's CodeFirst is a good thing, so we don't have to worry about the database side at all (note that we don't need to know about the database), and everything can be done in code. EF is ORM, and the data access operation is well encapsulated and can be used directly in the business layer, so why do we encapsulate it so much? In my opinion, encapsulation can bring at least the following benefits:

The related objects of EF are encapsulated in the data access layer, which frees the business layer from the dependence on EF.

Unify the data operation of EF to ensure that the business layer uses the same code specification

Hide the sensitive configuration of EF to reduce the difficulty of using EF

Here we introduce a question: how to encapsulate EF, not only to ensure the unity and convenience of use, but also to maintain the convenience of EF, otherwise, encapsulation will become an obstacle to the business layer. Next, it mainly analyzes the possible misuse of data query.

Second, the analysis of query problems

(1) where should data query be done?

In EF, object-oriented data query provides two main ways:

TEntity DbSet.Find (params object [] keyValues): searching for a single entity through the primary key is first queried in the local dataset Local of EF, and then queried in the database if not.

Extension methods for all data queries of IQueryable and IEnumerable types (because DbSet inherits from IQueryable and IEnumerable), such as SingleOrDefault,FirstOrDefault,Where, etc. Among them, the expansion method of IQueryable will first collect the requirements, and then generate the corresponding SQL statement for data query in the last step, while the expansion method of IEnumerable will generate the corresponding SQL statement to obtain data to memory in the first step of the query, and the subsequent operations are based on the data in memory.

The above two methods provide a great degree of freedom for EF data query, which we need to maintain when encapsulating. However, in reading the encapsulation of EF by many people (some of whom have been working for several years) and designing a unified data operation interface Repository, the following mistakes are usually made:

A lot of GetByName,GetByXX,GetByXXX operations are designed, which are not usually used by all entities, but are only used by part of the business of some entities, or are "expected to be used".

SingleOrDefault,FirstOrDefault,Count,GetByPredicate (predicate) and other methods for conditional query are defined, but it is very difficult to use Expression or Func for the type of conditional predicate. In the end, both are designed, which is equivalent to going through IQueryable,IEnumerable 's method again.

The GetAll () method to get all the data is defined, but the return value of IEnumerable type is used, which is equivalent to loading the data of the whole table into memory. The problem is very serious, but the designer does not know it.

Such as this, a variety of bizarre query operations emerge one after another, which either destroy the original flexibility of EF data query, or icing on the cake.

In fact, there is only one reason for so many mistakes. Designers forget that EF is ORM and use EF as ado.net. As long as you remember that EF is ORM, the above functions have been implemented, so don't repeat them. Then the above problems can be solved very easily, as long as:

In the data manipulation Repository interface, the DbSet of EF is exposed as a read-only IQueryable type attribute to the business layer as a data source for data query.

Just do it. This data source is read-only and the type is IQueryable, which ensures that it can only be used as a data source for data query, unlike the open DbSet type, which can call the internal methods of EF in the business layer to add, delete, modify and other operations. In addition, the IQueryable type keeps the original query freedom and flexibility of EF, simple and clear. This data set can also be passed to all levels of the business layer to achieve the flexibility to look up the data wherever it is needed.

(2) query traps in loops

The navigation property of EF is delayed loading. The advantage of delayed loading is that it does not need to load only the necessary data at a time, which reduces the amount of data loaded each time, but the disadvantage is self-evident: it greatly increases the number of database connections, such as the following simple requirement:

Output the number of roles each user has

According to this requirement, it is easy to write the following code:

Traverses all user information and outputs the number of roles (navigation attributes) in each user information.

The logic of the above code is clear and there seems to be no problem. Let's analyze the execution of the code:

Line 132, get an instance of the user repository interface from the IOC container, which is fine.

Line 133, fetch all the user information (memberRepository.Entities), and execute SQL as follows:

SELECT [Extent1]. [Id] AS [Id], [Extent1]. [UserName] AS [UserName], [Extent1]. [Password] AS [Password], [Extent1]. [NickName] AS [NickName], [Extent1]. [Email] AS [Email], [Extent1]. [IsDeleted] AS [IsDeleted], [Extent1]. [AddDate] AS [AddDate], [Extent1]. 1654116431 AS 1654116431 [Extent2]. [Id] AS [Id1] FROM [dbo]. [Members] AS [Extent1] LEFT OUTER JOIN [dbo]. [MemberExtends] AS [Extent2] ON [Extent1]. [Id] = [Extent2]. [Member_Id]

Although the SQL generated by EF is a little complicated, there is no problem.

3. 136 lines, there will be a problem, each loop will connect to the database, execute the following query (the last 1 is the user number):

Exec sp_executesql N'SELECT [Extent2]. [Id] AS [Id], [Extent2]. [Name] AS [Name], [Extent2]. [Description] AS [Description], [Extent2]. [RoleTypeNum] AS [RoleTypeNum], [Extent2]. [IsDeleted] AS [IsDeleted], [Extent2]. [AddDate] AS [AddDate] [Extent2]. 1654116431 AS 1654116431 FROM [dbo]. [RoleMembers] AS [Extent1] INNER JOIN [dbo]. [Roles] AS [Extent2] ON [Extent1]. [Role_Id] = [Extent2]. [Id] WHERE [Extent1]. [Member_Id] = @ EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

Just imagine, if you have 100 users, you have to connect to the database 100 times. Such a simple requirement, connect to the database 101 times, must not make the database crazy.

Of course, some students can say that delayed loading is used here to increase the number of connections to the database. You can load immediately and load the Role role at one time. All right, let's take a look at loading immediately:

Line 143When fetching all the user information, use the Include method to query all the role information associated with the user, so that you will not connect to the database to query the role information when traversing. But if you see the execution of the SQL statement, you probably want to die. The query executed is as follows:

SELECT [Project1]. [Id] AS [Id], [Project1]. [UserName] AS [UserName], [Project1]. [Password] AS [Password], [Project1]. [NickName] AS [NickName], [Project1]. [Email] AS [Email], [Project1]. [IsDeleted] AS [IsDeleted], [Project1]. [AddDate] AS [AddDate], [Project1]. 1654116431 AS 1654116431, [Project1]. [Id1] AS [Id1] [Project1]. [C1] AS [C1], [Project1]. [Id2] AS [Id2], [Project1]. [Name] AS [Name], [Project1]. [Description] AS [Description], [Project1]. [RoleTypeNum] AS [RoleTypeNum], [Project1]. [IsDeleted1] AS [IsDeleted1], [Project1]. [AddDate1] AS [AddDate1], [Project1]. [Timestamp1] AS [Timestamp1] FROM (SELECT [Extent1]. [Id] AS [Id] [Extent1]. [UserName] AS [UserName], [Extent1]. [Password] AS [Password], [Extent1]. [NickName] AS [NickName], [Extent1]. [Email] AS [Email], [Extent1]. [IsDeleted] AS [IsDeleted], [Extent1]. [AddDate] AS [AddDate], [Extent1]. 1654116431 AS 1654116431, [Extent2]. [Id] AS [Id1] [Join2]. [Id] AS [Id2], [Join2]. [Name] AS [Name], [Join2]. [Description] AS [Description], [Join2]. [RoleTypeNum] AS [RoleTypeNum], [Join2]. [IsDeleted] AS [IsDeleted1], [Join2]. [AddDate] AS [AddDate1], [Join2]. 1654116431 AS [Timestamp1] CASE WHEN ([Join2]. [Member_Id] IS NULL) THEN CAST (NULL AS int) ELSE 1 END AS [C1] FROM [dbo]. [Members] AS [Extent1] LEFT OUTER JOIN [dbo]. [MemberExtends] AS [Extent2] ON [Extent1]. [Id] = [Extent2]. [Member_Id] LEFT OUTER JOIN (SELECT [Extent3]. [Member_Id] AS [Member_Id], [Extent4]. [Id] AS [Id] [Extent4]. [Name] AS [Name], [Extent4]. [Description] AS [Description], [Extent4]. [RoleTypeNum] AS [RoleTypeNum], [Extent4]. [IsDeleted] AS [IsDeleted], [Extent4]. [AddDate] AS [AddDate] [Extent4]. 1654116431 AS 1654116431 FROM [dbo]. [RoleMembers] AS [Extent3] INNER JOIN [dbo]. [Roles] AS [Extent4] ON [Extent4]. [Id] = [Extent3]. [Role_Id]) AS [Join2] ON [Extent1]. [Id] = [Join2]. [Member_Id]) AS [Project1] ORDER BY [Project1]. [Id] ASC, [Project1]. [Id1] ASC, [Project1]. [C1] ASC

(3) query traps for navigation attributes

Let's review what the navigation properties look like (take the role information in the user information as an example):

As you can see, the navigation property of the collection class is a collection of ICollection types, and its implementation class can be usually using List or HashSet. With ICollection, the navigation property of the collection class is limited to a memory collection, and as long as this navigation property is used, all the data in the collection must be loaded into memory before subsequent operations can be carried out. For example, in the above example, we just want to know the number of roles the user has, and the original intention is to execute the Count statement of SQL, but we did not expect that EF would load this collection into memory (the above statement is to query all the role information of the current user) and then count it in memory, which is virtually a great waste of resources. For example, in a mall system, if we want to know the sales volume of a product (product.Orders.Count), we may load tens of thousands of order information into memory and count it, which will be a catastrophic resource consumption.

After reading this, are you very disappointed with EF?

Third, how should the query be designed

The above problems are not a problem at all during the development phase of the project, because the software can still run and run well. But when the website is online and the number of users comes up, these performance killers will be fully exposed. It's a problem. We have to find a way to solve it.

Let's talk about my solution. As to whether the solution is reliable or not, it is up to the reader to judge for himself.

(1) query dataset design

In the previous design, the entity's data warehouse interface has exposed an IQueryable interface to the upper layer, and there is a lot about why to expose this interface. Next, taking the account module as an example, let's take a look at how to pass this query dataset up.

First of all, don't forget that our project structure looks like this:

Protect the injected Repository interface

In the core business implementation class (AccountService), we inject the Repository interface of each related entity

It should be noted that the Repository interface of an entity can only be used in the business layer to prevent developers from calling data operations such as additions, deletions and modifications in the presentation layer to implement the business, rather than implementing the business in the business layer. Therefore, the Repository interface property accessibility of the injected entity is changed to protected.

Open query datasets for use in the presentation layer

The Repository interface in the business layer is set to protected, so the IEntityRepository.Entities dataset cannot be accessed in the presentation layer. How to query the data in the presentation layer is very simple, as long as the IEntityRepository.Entities dataset is packaged into an IQueryable query dataset in the business interface and developed.

Wrap the IEntityRepository.Entities dataset in the business implementation class:

After this encapsulation, in the business layer, we can use the IEntityRepository.Entities dataset for data query, and in the presentation layer, we can use the open dataset in the business contract for query. Because the developed dataset is still of IQueryable type, there is no loss of query freedom for EF.

(2) the solution to the query trap

For the EF query trap mentioned earlier, my proposed solution is

Query on demand through IQueryable's Select (selector) extension method.

First of all, analyze what data is needed and what you want in the current business, and the final data is loaded with anonymous objects.

For example, the previously mentioned requirement for outputting the number of roles owned by users is as follows:

The query statement executed by the above code is as follows:

SELECT [Extent1]. [Id] AS [Id], (SELECT COUNT (1) AS [A1] FROM [dbo]. [RoleMembers] AS [Extent2] WHERE [Extent1]. [Id] = [Extent2]. [Member_Id]) AS [C1] FROM [dbo]. [Members] AS [Extent1]

It's pretty simple, and that's what we need.

(3) comparison between anonymous object scheme and entity object scheme

Although the anonymous object scheme has achieved the desired effect, what is the difference between the entity object scheme and the entity object scheme? let's compare it below:

Data transmissibility, reusability:

-Anonymous objects: basically one-time data, which cannot be transmitted as a whole and cannot be reused.

+ entity object: transitive and reusable.

Support for refactoring and method extraction:

Anonymous object: because the data cannot be passed, the written code is difficult to ReFactor. I have written hundreds of lines of code and cannot extract the method of submethod refactoring.

+ entity objects: data supports code refactoring and method extraction well.

Impact on cache hit ratio:

-Anonymous objects: the data is closely related to specific business scenarios (parameters, conditions, etc.), and the cache hit rate may be low.

+ entity objects: the data is easy to reuse and the cache hit rate may be high.

Automatic mapping transformation of data models at different levels (AutoMapper, etc.)

-Anonymous object: the attribute and type are uncertain, so it is difficult to convert.

+ entity object: easy to implement mapping transformation.

Data utilization:

+ Anonymous objects: data is obtained on demand, with high utilization and basically no waste.

-entity objects: all the data are taken out as a whole, resulting in low utilization and great waste.

Program performance impact:

+ Anonymous objects: easy to write code that runs efficiently and has good performance.

-entity objects: it is easy to write code with low performance.

Through the above comparison, I hope to provide some reference for the choice of the scheme. As for how to choose and finally choose what scheme, we can only weigh it according to the characteristics of the business and use which one is suitable.

IV. Realization of requirements

As I have said many times before, it is clearly mentioned here that in this architecture design, if the existing query method can not meet the business requirements, you need to add a corresponding query function, and you do not need to operate in the data layer. You just need:

Extend IQueryable to add an extension method to IQueryable.

(1) sort by attribute name

The query is inseparable from the paging query, which is usually sorted before the paging query, and then find out the single-page data of the specified page. let's talk about the problem of sorting by attributes.

Sorting can be done using IQueryable's OrderBy and OrderByDescending extension methods, such as:

Source.OrderBy (m = > m.AddDate) .ThenByDescending (m = > m.IsDeleted)

This is the sorting method provided by the system, but only supports parameters of type Expression keySelector, and when we click on the header of the table, we usually get the string of the attribute name of the entity, so we also need to extend a sorting method that supports the attribute name.

First, define a class to encapsulate the sort condition, which usually includes the attribute name and the sort direction:

Namespace GMF.Component.Tools {/ attribute sort condition information class / public class PropertySortCondition {/ construct an ascending sort condition of the specified attribute name / sort attribute name public PropertySortCondition (string propertyName): this (propertyName ListSortDirection.Ascending) {} / construct a sort condition of sort attribute name and sort method / sort attribute name / sort method public PropertySortCondition (string propertyName, ListSortDirection listSortDirection) {PropertyName = propertyName ListSortDirection = listSortDirection;} / get or set the sort property name / public string PropertyName {get; set;} / get or set the sort direction / public ListSortDirection ListSortDirection {get; set }}}

Second, we receive a string whose sort condition is the name of the attribute, and we actually want to call the sorting method of the Expression keySelector type parameter provided by the system for sorting. So we also need a method to convert the string condition to a sort expression and call the system's sort method.

Private static class QueryableHelper {/ / ReSharper disable StaticFieldInGenericType private static readonly ConcurrentDictionary Cache = new ConcurrentDictionary (); internal static IOrderedQueryable OrderBy (IQueryable source, string propertyName, ListSortDirection sortDirection) {dynamic keySelector = GetLambda_Expression (propertyName); return sortDirection = = ListSortDirection.Ascending? Queryable.OrderBy (source, keySelector): Queryable.OrderByDescending (source, keySelector);} internal static IOrderedQueryable ThenBy (IOrderedQueryable source, string propertyName, ListSortDirection sortDirection) {dynamic keySelector = GetLambda_Expression (propertyName); return sortDirection = = ListSortDirection.Ascending? Queryable.ThenBy (source, keySelector): Queryable.ThenByDescending (source, keySelector);} private static LambdaExpression GetLambda_Expression (string propertyName) {if (Cache.ContainsKey (propertyName)) {return Cache [propertyName];} ParameterExpression param = Expression.Parameter (typeof (T)) MemberExpression body = Expression.Property (param, propertyName); LambdaExpression keySelector = Expression.Lambda (body, param); Cache [propertyName] = keySelector; return keySelector;}}

At this point, with the previous preparation, the sorting of attribute names is very easy to write. For ease of use, you should make the extension method of IQueryable:

/ / sort the IQueryable [T] collection according to the specified attributes and sort mode / / sort the dataset to be sorted / sort attribute name / / sort direction / dynamic type / sorted dataset public static IOrderedQueryable OrderBy (this IQueryable source, string propertyName) ListSortDirection sortDirection = ListSortDirection.Ascending) {PublicHelper.CheckArgument (propertyName, "propertyName") Return QueryableHelper.OrderBy (source, propertyName, sortDirection) } / sort the IQueryable [T] collection according to the specified property sort condition / dynamic type / dataset / list property sort condition / public static IOrderedQueryable OrderBy (this IQueryable source, PropertySortCondition sortCondition) {PublicHelper.CheckArgument (sortCondition, "sortCondition") Return source.OrderBy (sortCondition.PropertyName, sortCondition.ListSortDirection) } / continue to sort the IOrderedQueryable [T] collection according to the specified property sort / dynamic type / dataset to be sorted / sort property name / / sort direction / public static IOrderedQueryable ThenBy (this IOrderedQueryable source, string propertyName ListSortDirection sortDirection = ListSortDirection.Ascending) {PublicHelper.CheckArgument (propertyName, "propertyName") Return QueryableHelper.ThenBy (source, propertyName, sortDirection) } / continue to sort the IOrderedQueryable [T] collection by specifying the property sort method / dynamic type / dataset / list property sort condition / public static IOrderedQueryable ThenBy (this IOrderedQueryable source, PropertySortCondition sortCondition) {PublicHelper.CheckArgument (sortCondition, "sortCondition") Return source.ThenBy (sortCondition.PropertyName, sortCondition.ListSortDirection);}

ListSortDirection is used to indicate the sort direction, and of course, you can also define ThenByDescending extension methods to reverse sort. The above sort can be written as follows:

Source.OrderBy ("AddDate") .ThenBy ("IsDeleted", ListSortDirection.Descending)

(2) pagination query

Let's talk about paging query. Usually, the design method of paging query is to define a specific method to obtain paging data in the warehousing operation Repository. Now we are dealing with IQueryable data sets, so we don't have to be so troublesome. You only need to define an extension method that is dedicated to paging queries. The code is as follows:

/ continue to sort the IOrderedQueryable [T] collection by specifying the property sort method / dynamic type / / dataset to be sorted / list property sort condition / public static IOrderedQueryable ThenBy (this IOrderedQueryable source, PropertySortCondition sortCondition) {PublicHelper.CheckArgument (sortCondition, "sortCondition") Return source.ThenBy (sortCondition.PropertyName, sortCondition.ListSortDirection) } / query the subdata set of the specified paging condition from the specified IQueryable [T] collection / dynamic type / dataset to be queried / query condition predicate expression / paging index / paging size / / / output the total number of records that meet the criteria / sort the set of conditions / public static IQueryable Where (this IQueryable source Expression predicate, int pageIndex, int pageSize, out int total, PropertySortCondition [] sortConditions = null) where T: Entity {PublicHelper.CheckArgument (source, "source") PublicHelper.CheckArgument (predicate, "predicate"); PublicHelper.CheckArgument (pageIndex, "pageIndex"); PublicHelper.CheckArgument (pageSize, "pageSize"); total = source.Count (predicate); if (sortConditions = = null | | sortConditions.Length = = 0) {source = source.OrderBy (m = > m.AddDate) } else {int count = 0; IOrderedQueryable orderSource = null; foreach (PropertySortCondition sortCondition in sortConditions) {orderSource = count = = 0? Source.OrderBy (sortCondition.PropertyName, sortCondition.ListSortDirection): orderSource.ThenBy (sortCondition.PropertyName, sortCondition.ListSortDirection); count++;} source = orderSource;} return source! = null? Source.Where (predicate) .Skip ((pageIndex-1) * pageSize) .Take (pageSize): Enumerable.Empty () .AsQueryable ();}

In this way, to get a page of data, you can simply call this extension method, which is as convenient as calling the system's extension method (where total is the total number of records).

Int total; var pageData = source.Where (m = > m.IsDeleted, 4, 20, out total)

(3) query actual combat

Next, let's take a look at the data query.

First, the data we want to query will be displayed in the following class, where LoginLogCount is the number of logins of the current user and RoleNames is the collection of role names owned by the user, both of which are derived from other tables associated with Member.

Namespace GMF.Demo.Site.Models {public class MemberView {public int Id {get; set;} public string UserName {get; set;} public string NickName {get; set;} public string Email {get; set;} public bool IsDeleted {get; set;} public DateTime AddDate {get; set;} public int LoginLogCount {get; set } public IEnumerable RoleNames {get; set;}

In order to simplify the demonstration operation, the paging control MVCPager is introduced to handle the processing of the paging bar on the page.

The code in Controller is as follows. Pay attention to the query code for data acquisition:

Namespace GMF.Demo.Site.Web.Controllers {[Export] public class HomeController: Controller {[Import] public IAccountSiteContract AccountContract {get; set;} public ActionResult Index (int? Id) {int pageIndex = id? 1; const int pageSize = 20; PropertySortCondition [] sortConditions = new [] {new PropertySortCondition ("Id")}; int total Var memberViews = AccountContract.Members.Where (m = > true, pageIndex, pageSize, out total, sortConditions) .Select (m = > new MemberView {UserName = m.UserName, NickName = m.NickName, Email = m.Email, IsDeleted = m.IsDeleted, AddDate = m.AddDate LoginLogCount = m.LoginLogs.Count, RoleNames = m.Roles.Select (n = > n.Name)}) PagedList model = new PagedList (memberViews, pageIndex, pageSize, total); return View (model);}}

Although MVCPager is used here, her paging feature is not used. We did the paging process ourselves, but used her single-page data model class PageList as the view model.

The View code is as follows:

@ using Webdiyer.WebControls.Mvc; @ using GMF.Component.Tools; @ model PagedList @ {ViewBag.Title = "Index"; Layout = "~ / Views/Shared/_Layout.cshtml" } Index @ if (! User.Identity.IsAuthenticated) {@ Html.ActionLink ("login", "Login", "Account")} else {user @ User.Identity.Name has logged in @ Html.ActionLink ("exit", "Logout") "Account")} UserName NickName Email IsDeleted AddDate LoginLogCount RoleNames @ foreach (var item in Model) {@ Html.DisplayFor (modelItem = > item.UserName) @ Html.DisplayFor (modelItem = > item.NickName) @ Html.DisplayFor (modelItem = > item.Email) Html.DisplayFor (modelItem = > item.IsDeleted) @ Html.DisplayFor (modelItem = > item.AddDate) @ Html.DisplayFor (modelItem = > item.LoginLogCount) @ item.RoleNames.ExpandAndToString (" ")} @ Html.Pager (Model, new PagerOptions {PageIndexParameterName =" id "})

The display effect is as follows:

The SQL statement executed by the query is as follows:

SELECT [Project2]. [Id] AS [Id], [Project2]. [UserName] AS [UserName], [Project2]. [NickName] AS [NickName], [Project2]. [Email] AS [Email], [Project2]. [IsDeleted] AS [IsDeleted], [Project2]. [AddDate] AS [AddDate], [Project2]. [C2] AS [C1], [Project2]. [C1] AS [C2] [Project2]. [Name] AS [Name] FROM (SELECT [Limit1]. [Id] AS [Id], [Limit1]. [UserName] AS [UserName], [Limit1]. [NickName] AS [NickName], [Limit1]. [Email] AS [Email], [Limit1]. [IsDeleted] AS [IsDeleted], [Limit1]. [AddDate] AS [AddDate], [Join1]. [Name] AS [Name] CASE WHEN ([Join1]. [Member_Id] IS NULL) THEN CAST (NULL AS int) ELSE 1 END AS [C1], [Limit1]. [C1] AS [C2] FROM (SELECT TOP (20) [Project1]. [Id] AS [Id], [Project1]. [UserName] AS [UserName], [Project1]. [NickName] AS [NickName], [Project1]. [Email] AS [Email], [Project1]. [IsDeleted] AS [IsDeleted] [Project1]. [AddDate] AS [AddDate], [Project1]. [C1] AS [C1] FROM (SELECT [Project1]. [Id] AS [Id], [Project1]. [UserName] AS [UserName], [Project1]. [NickName] AS [NickName], [Project1]. [Email] AS [Email], [Project1]. [IsDeleted] AS [IsDeleted], [Project1]. [AddDate] AS [AddDate], [Project1]. [C1] AS [C1] Row_number () OVER (ORDER BY [Project1]. [Id] ASC) AS [row_number] FROM (SELECT [Extent1]. [Id] AS [Id], [Extent1]. [UserName] AS [UserName], [Extent1]. [NickName] AS [NickName], [Extent1]. [Email] AS [Email] [Extent1]. [IsDeleted] AS [IsDeleted], [Extent1]. [AddDate] AS [AddDate] (SELECT COUNT (1) AS [A1] FROM [dbo]. [LoginLogs] AS [Extent2] WHERE [Extent1]. [Id] = [Extent2]. [Member_Id]) AS [C1] FROM [dbo]. [Members] AS [Extent1]) AS [ Project1]) AS [Project1] WHERE [Project1]. [row_number] > 0 ORDER BY [Project1]. [Id] ASC) AS [Limit1] LEFT OUTER JOIN (SELECT [Extent3]. [Member_Id] AS [Member_Id] [Extent4]. [Name] AS [Name] FROM [dbo]. [RoleMembers] AS [Extent3] INNER JOIN [dbo]. [Roles] AS [Extent4] ON [Extent4]. [Id] = [Extent3]. [Role_Id]) AS [Join1] ON [Limit1]. [Id] = [Join1]. [Member_Id]) AS [Project2] ORDER BY [Project2]. [Project2] ASC, [Project2]. [C1] ASC

Although the SQL statement executed is relatively complex, it is indeed the simplest query according to our requirements. For example, if we do not query the Password attribute of Member, there is no statement related to Password, the count of LoginLog, the filtering of the Name attribute of Roles, and no query involving other attributes of this class.

These are all the contents of the article "how to query data in EF Code First". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.