public void CanPerformSomeInMermoryAndSomeInDatabase()
{
using (var db = new EF7BloggContext(ServiceProvider))
{
//EnsureCleanDatabase(db);
db.Blogs.Add(new Blog()
{
Name = "ADO.NET Blog"
});
db.Blogs.Add(new Blog()
{
Name = "Visual Studio"
});
db.Blogs.Add(new Blog()
{
Name = ".NET Blog"
});
db.SaveChanges();
var seachedBlogs = db.Set <Blog>()
.OrderBy(b => GetSortName(b.Name))
.Where(b => b.Name.Contains(("blog"))); // Some parts of the query in db (where) and some inmemory (GetSortName)
foreach (var blog in seachedBlogs)
{
var found = blog.Name;
}
}
}
public void CanIncludeThenIncludeNavigationProperties()
{
using (var db = new EF7BloggContext(ServiceProvider))
{
//EnsureCleanDatabase(db);
db.Blogs.Add(new Blog()
{
Name = "ADO.NET Blog"
});
db.Blogs.Add(new Blog()
{
Name = "Visual Studio"
});
db.Blogs.Add(new Blog()
{
Name = ".NET Blog"
});
db.SaveChanges();
var seachedBlogs = db.Set <Blog>()
.Include((b => b.Posts)) // Eager loading, in ef6 10 blog with 10 posts each
.Where(b => b.Name.Contains("blog"));
foreach (var blog in seachedBlogs)
{
var found = blog.Name;
}
}
}
public void CanSelectDynamic()
{
using (var db = new EF7BloggContext())
{
var authorToSerachFor = Fixture.Create <string>();
var blog = new Blog()
{
Name = Fixture.Create <string>(), Description = Fixture.Create <string>()
};
db.AddRange(new[] { blog });
db.SaveChanges();
var retrievedBlog = db.Set <Blog>()
.Include(p => p.Posts)
.ToList();
//.Select(p => new { p.Author })
//.ToList();
//.SelectDynamic(new[] { nameof(Blog.Author) });
foreach (var blog1 in retrievedBlog)
{
Assert.IsNotNull(blog1);
}
}
}
public void CanRetriveUsingTableValuedFunction()
{
/*
* USE [EF7]
* GO
*
* SET ANSI_NULLS ON
* GO
*
* SET QUOTED_IDENTIFIER ON
* GO
*
* DROP FUNCTION[dbo].[SearchBlogs];
*
* GO
* CREATE FUNCTION[dbo].[SearchBlogs]
* (
* @param2 nvarchar(200)
* )
* RETURNS @returntable TABLE
* (
* Id int,
* Author nvarchar(200),
* Name nvarchar(200),
* Description nvarchar(200)
* )
* AS
* BEGIN
* INSERT @returntable
* SELECT
* blogs.Id, blogs.Name, blogs.Author, blogs.Description
* from dbo.Blog blogs
* where
* blogs.Name like @param2
* RETURN
* END
*/
using (var db = new EF7BloggContext(ServiceProvider))
{
//EnsureCleanDatabase(db);
db.Blogs.Add(new Blog()
{
Name = "ADO.NET Blog"
});
db.Blogs.Add(new Blog()
{
Name = "Visual Studio"
});
db.Blogs.Add(new Blog()
{
Name = ".NET Blog"
});
db.SaveChanges();
/*
* 1. Parameterized, not open to sql injection
* 2. Strongly typed (renaming properties)
* 3. The order by is happening in the database, not in memory.
*/
var nameToSerachFor = "blog";
var seachedBlogs = db.Set <Blog>()
.FromSql("select * from dbo.SearchBlogs (@p0)", nameToSerachFor);
//.OrderBy(b => b.Name); // <-- Strongly typed
foreach (var blog in seachedBlogs)
{
var found = blog.Name;
}
}
}