EF Core uses Linq to query data.
Basic Query
Microsoft provides more than 100 examples to demonstrate queries at https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b
We can make a simple query through the following code:
//Get all the data
var blogs = context.Blogs.ToList();
//Getting a single entity
var blog = context.Blogs.Single(b => b.BlogId == 1);
//screen
var blogs = context.Blogs
.Where(b => b.Url.Contains("dotnet"))
.ToList();Loading associated data
EF Core has three common models to load associated data:
- Preloading: Represents loading associated data from a database as part of an initial query
- Explicit loading: Represents later explicit loading of associated data from the database
- Delayed loading: Represents loading associated data from a database when accessing it
Preloading
Use the Include method to specify the associated data to be included in the query results. For example:
using (var context = new BloggingContext())
{
var blogs = context.Blogs
.Include(blog => blog.Posts)
.Include(blog => blog.Owner)
.ToList();
}Associated data can be hierarchical, and ThenInclude can be invoked in a chain to further contain deeper levels of associated data. :
using (var context = new BloggingContext())
{
var blogs = context.Blogs
.Include(blog => blog.Posts)
.ThenInclude(post => post.Author)
.ThenInclude(author => author.Photo)
.Include(blog => blog.Owner)
.ThenInclude(owner => owner.Photo)
.ToList();
}If you change the query so that it no longer returns an instance of the entity type that the query starts with, the include operator is ignored. For example:
using (var context = new BloggingContext())
{
var blogs = context.Blogs
.Include(blog => blog.Posts)
.Select(blog => new
{
Id = blog.BlogId,
Url = blog.Url
})
.ToList();
}At this point, EF Core ignores the inclusion and generates a warning log.
Explicit Loading
Navigation properties are explicitly loaded through the DbContext.Entry(...) API. For example:
using (var context = new BloggingContext())
{
var blog = context.Blogs
.Single(b => b.BlogId == 1);
context.Entry(blog)
.Collection(b => b.Posts)
.Load();
context.Entry(blog)
.Reference(b => b.Owner)
.Load();
}Delayed loading
The simplest way to use lazy loading is to install the Microsoft. Entity Framework Core. Proxies package and enable it by calling UseLazy Loading Proxies. For example:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseLazyLoadingProxies()
.UseSqlServer(myConnectionString);Or in Service Configure, when calling the services.AddDbContext method:
services.AddDbContext<BloggingContext>(
b => b.UseLazyLoadingProxies()
.UseSqlServer(myConnectionString));EF Core lazy loading requires that attributes must be shared and have virtual modifiers, so that they can be overridden by subclasses. Why do you want to do this? Refer to my previous article.< Basic Use of Castle DynamicProxy (AOP)>.
The following code demonstrates the use of lazy loading:
public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Post> Posts { get; set; }
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public virtual Blog Blog { get; set; }
}EF Core uses proxy classes to lazily load data.
EF Core also provides delayed loading without proxy, which requires injecting ILazyLoader instances into entity classes and implementing get access through the instances:
public class Blog
{
private ICollection<Post> _posts;
public Blog()
{
}
private Blog(ILazyLoader lazyLoader)
{
LazyLoader = lazyLoader;
}
private ILazyLoader LazyLoader { get; set; }
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Post> Posts
{
get => LazyLoader.Load(this, ref _posts);
set => _posts = value;
}
}This approach requires injection of ILazyLoader, which results in more package dependencies.
Delayed loading with EF Core may result in circular references, which cannot be serialized using Json.Net at this time. Some configuration is needed for this:
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc()
.AddJsonOptions(
options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
);
}Client vs. Server
EF Core supports partial queries on the client side and some queries are sent to the server, which may cause performance problems.
EF Core warns when client filtering occurs, or it can configure an exception to be thrown when client filtering occurs:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
.ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}Tracking and non-tracking
By default, EF Core tracks the entities returned by the query, and if we do not need to track the entities returned by the query, tracing can be disabled through the AsNoTracking method.
using (var context = new BloggingContext())
{
var blogs = context.Blogs
.AsNoTracking()
.ToList();
}Or disable tracing at the DbContext level:
using (var context = new BloggingContext())
{
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var blogs = context.Blogs.ToList();
}When using projected query results, if an entity type is included, the entity type is tracked, such as the following query, which tracks blogs and Post s:
using (var context = new BloggingContext())
{
var blog = context.Blogs
.Select(b =>
new
{
Blog = b,
Posts = b.Posts.Count()
});
}In addition, if the query results do not contain any entity types, tracing is not performed. For example:
using (var context = new BloggingContext())
{
var blog = context.Blogs
.Select(b =>
new
{
Id = b.BlogId,
Url = b.Url
});
}Original SQL Query
When Linq can not meet the query requirements, or because the use of Linq to generate less efficient SQL queries, you can consider using the original SQL query. EF Core supports raw SQL statements and stored procedures.
The original SQL statement:
var blogs = context.Blogs
.FromSql("SELECT * FROM dbo.Blogs")
.ToList();Stored procedures:
var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogs")
.ToList();Parameter transfer
When querying with raw SQL, parameterized queries must be used to resist SQL injection attacks.
The good thing is that EF Core considers how to defend against SQL injection attacks when we design it, so when we use FromSql method, if splicing strings are used in parameters, it will automatically generate SQL query parameters for us, for example:
var user = "johndoe";
var blogs = context.Blogs
.FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
.ToList();Although the above SQL statement looks like a directly concatenated string, EF Core has actually generated query parameters for us.
Of course, we can also create query parameters manually:
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
.ToList();When named parameters are used in database stored procedures, it is useful to create query parameters manually:
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogs @filterByUser=@user", user)
.ToList();Stitching Linq
When we use the original SQL query, EF Core still supports us to write query statements using linq. When executing queries, EF Core checks whether our SQL statements support splicing, and if so, splicing LINQ filtering statements into SQL and sending them to the database for query.
Track
Tracking in the original SQL is consistent with that in Linq queries.
Associated data
The way to query the associated data in the original SQL is the same as that of Linq query.
Global filter
Global filters are very useful for soft deletion and multi-tenancy. The definition is as follows:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");
// Configure entity filters
modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}We can disable global filters in specific queries:
blogs = db.Blogs
.Include(b => b.Posts)
.IgnoreQueryFilters()
.ToList();