شروع به کار با EF Core 1.0 - قسمت 10 - استفاده از امکانات بومی بانکهای اطلاعاتی
نویسنده: وحید نصیری
تاریخ: ۱۳۹۵/۰۶/۰۷ ۱۴:۳۰
آدرس: www.dntips.ir
| مطالب | ۳۶۹۴ |
| نویسندگان | ۲۷۶ |
| گروههای مطالب | ۱۰۲۴ |
| نقشههای راه | ۱۱۹ |
| دورهها | ۱۴ |
| اشتراکها | ۱۷۹۱۴ |
var blogs = context.Blogs
.FromSql("SELECT * FROM dbo.Blogs")
.ToList(); var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogs")
.ToList(); var user = "johndoe";
var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
.ToList(); var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
.ToList(); var results = _context.Contacts.FromSql(
@"SELECT Id, Name Address, City, State, Zip
FROM Contacts
WHERE Name IN (@p0, @p1)", name1, name2); var nameParameter = new SqlParameter
{
ParameterName = "@name",
Value = "doc",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.NVarChar
}; var posts = context.Posts
.FromSql("SELECT * FROM dbo.GetMatchingPostByTitle({0})", searchTerm)
.Where(p => p.BlogId == 1)
.OrderByDescending(p => p.CreateDate)
.ToList(); var searchTerm = ".NET";
var blogs = context.Blogs
.FromSql("SELECT * FROM dbo.SearchBlogs {0}", searchTerm)
.Include(b => b.Posts)
.ToList(); context.Database.ExecuteSqlCommand("UPDATE dbo.People SET FirstName = 'Jane' WHERE PersonId = 30"); context.Database.ExecuteSqlCommand("usp_CreateShipper @p0, @p1",
parameters: new[] { "hello", "world" }); using (var connection = context.Database.GetDbConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM Contacts";
var result = command.ExecuteScalar().ToString();
}
} var userName = "user 1";
var user = context.Users.FromSql($"select top 1 * from Users where name = {userName} ").FirstOrDefault();
if (user != null)
{
Console.WriteLine(user.Name);
} SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name]
FROM (
select top 1 * from Users where name = @p0
) AS [u] var sql = $"select top 1 * from Users where name = {userName} ";
user = context.Users.FromSql(sql).FirstOrDefault();
if (user != null)
{
Console.WriteLine(user.Name);
} .SqlException: Incorrect syntax near '1'
public static IQueryable<TEntity> FromSql<TEntity>(this IQueryable<TEntity> source, FormattableString sql) where TEntity : class;
var sql = $"select top 1 * from Users where name = '{userName}' "; SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name] FROM ( select top 1 * from Users where name = 'user 1' ) AS [u]
var outParam = new SqlParameter("@ParamOut", DbType.Int32)
{ Direction = ParameterDirection.Output };
ctx.Database.ExecuteSqlCommand("EXEC dbo.MyTest @ParamOut OUTPUT", outParam); using (var db = new BloggingContext())
{
db.Database.ExecuteSqlCommand(
@"CREATE VIEW View_BlogPostCounts AS
SELECT Name, Count(p.PostId) as PostCount from Blogs b
JOIN Posts p on p.BlogId = b.BlogId
GROUP BY b.Name");
} public class BlogPostsCount
{
public string BlogName { get; set; }
public int PostCount { get; set; }
} public class BloggingContext : DbContext
{
public DbQuery<BlogPostsCount> BlogPostCounts { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Query<BlogPostsCount>().ToView("View_BlogPostCounts")
.Property(v => v.BlogName).HasColumnName("Name");
} using (var db = new BloggingContext())
{
var postCounts = db.BlogPostCounts.ToList(); private readonly IUnitOfWork _uow;
private readonly DbSet<Student> _student;
public StudentService(IUnitOfWork uow)
{
_uow = uow;
_uow.CheckArgumentIsNull(nameof(_uow));
_student = _uow.Set<Student>();
} var postCounts = await context.Database.SqlQuery<BlogPostsCount>(@$"SELECT * FROM View_BlogPostCounts").ToListAsync();
var postCounts = await context.Database
.SqlQuery<BlogPostsCount>(@$"SELECT * FROM View_BlogPostCounts")
.Where(x => x.PostCount > 1)
.ToListAsync();v1 = context.Customers.FromSql($"SELECT * FROM Customers WHERE City = {city}");
var sql = $"SELECT * FROM Customers WHERE City = {city}";
v2 = context.Customers.FromSql(sql); context.Products.FromSqlRaw(
"SELECT * FROM Products WHERE Name = {0}",
product.Name);
context.Products.FromSqlInterpolated(
$"SELECT * FROM Products WHERE Name = {product.Name}");