برای سایر محصولات میتونید به این صفحه مراجعه کنید: http://support.microsoft.com/gp/lifeselect
مثلا SQL Server: http://support.microsoft.com/lifecycle/?c2=1044
لیست محصولات موجود را دارد به همراه تاریخ نهایی منقضی شدن ساپورت آنها.
declare @t table(id int, name nvarchar(max), active bit) insert @t values (1, 'Group 1', 1), (2, 'Group 2', 0)
select '[' + STUFF(( select ',{"id":' + cast(id as varchar(max)) + ',"name":"' + name + '"' + ',"active":' + cast(active as varchar(max)) +'}' from @t t1 for xml path(''), type ).value('.', 'varchar(max)'), 1, 1, '') + ']'
[{"id":1,"name":"Group 1","active":1},{"id":2,"name":"Group 2","active":0}]
declare @group table(id int, name nvarchar(max), active bit) insert @group values (1, 'Group 1', 1), (2, 'Group 2', 0) declare @member table(id int, groupid int,name nvarchar(max)) insert @member values (1, 1,'Ali'), (2, 1,'Mojtaba'),(3,2,'Hamid') select '[' + STUFF(( select ',{"id":' + cast(g.id as varchar(max)) + ',"name":"' + g.name + '"' + ',"members": { "children": [' + (select + STUFF(( select ',{"id":' + cast(m.id as varchar(max)) + ',"name":"' + m.name + '"}' from @member m where m.groupid = g.id for xml path(''), type ).value('.', 'varchar(max)'), 1, 1, '') + ']}' + ',"active":' + cast(g.active as varchar(max)) +'}') from @group g for xml path(''), type ).value('.', 'varchar(max)'), 1, 1, '') + ']'
[{"id":1,"name":"Group 1","members": { "children": [{"id":1,"name":"Ali"},{"id":2,"name":"Mojtaba"}]} ,"active":1}, {"id":2,"name":"Group 2","members": { "children": [{"id":3,"name":"Hamid"}]} ,"active":0}]
update t set id = new_id from (select id, row_number() over(order by id) new_id from #temp)t --Cannot update identity column 'id'.
select id, identity(int, 1,1) new_id into #temptable from #temp order by id asc /* cannot add identity column, using the SELECT INTO statement, to table '#temptable', which already has column 'id' that inherits the identity property. */ update t set id = new_id from #temp t join #temptable d on t.id = d.id;
declare @t table(id int) insert into @t select id from #temp delete from #temp set identity_insert #temp on insert #temp (id) select row_number() over(order by id) from @t set identity_insert #temp off
using System.Collections.Generic; namespace AutoMapperComparison.Models { public class User { public int Id { get; set; } public string Name { get; set; } public ICollection<Address> Addresses { get; set; } } }
using System.ComponentModel.DataAnnotations.Schema; namespace AutoMapperComparison.Models { public class Address { public int Id { get; set; } public double? Code { get; set; } public string Title { get; set; } public int UserId { get; set; } [ForeignKey(nameof(UserId))] public virtual User User { get; set; } } }
using EntityFramework.BulkInsert.Extensions; using System.Collections.Generic; using System.Data.Entity; using System.Data.SqlClient; namespace AutoMapperComparison.Models { public class AppDbContextInitializer : DropCreateDatabaseAlways<AppDbContext> { protected override void Seed(AppDbContext context) { User user = context.Users.Add(new User { Name = "Test" }); context.SaveChanges(); List<Address> addresses = new List<Address>(); for (int i = 0; i < 500000; i++) { addresses.Add(new Address { Id = i, Code = 1, Title = "Test", UserId = user.Id }); } context.BulkInsert(addresses); base.Seed(context); } } public class AppDbContext : DbContext { static AppDbContext() { Database.SetInitializer(new AppDbContextInitializer()); //Database.SetInitializer<AppDbContext>(null); } public AppDbContext() : base(new SqlConnection(@"Data Source=.;Initial Catalog=AppDbContext;Integrated Security=True"), contextOwnsConnection: true) { Configuration.AutoDetectChangesEnabled = false; Configuration.EnsureTransactionsForFunctionsAndCommands = false; Configuration.LazyLoadingEnabled = false; Configuration.ProxyCreationEnabled = false; Configuration.ValidateOnSaveEnabled = false; Configuration.UseDatabaseNullSemantics = false; } public DbSet<User> Users { get; set; } public DbSet<Address> Addresses { get; set; } } }
namespace AutoMapperComparison.Models { public class AddressDto { public int Id { get; set; } public double? Code { get; set; } public string Title { get; set; } public int UserId { get; set; } public string UserName { get; set; } } }
using AutoMapper; using AutoMapper.QueryableExtensions; using AutoMapperComparison.Models; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; namespace AutoMapperComparison { public class Program { public static void Main() { Mapper.Initialize(cfg => { cfg.CreateMap<Address, AddressDto>(); }); Console.WriteLine($"Create Db {DateTimeOffset.UtcNow}"); using (AppDbContext db = new AppDbContext()) { db.Database.Initialize(force: true); db.Database.ExecuteSqlCommand("DBCC DROPCLEANBUFFERS"); //Removes all clean buffers from the buffer pool, and columnstore objects from the columnstore object pool Console.WriteLine(db.Addresses.ProjectTo<AddressDto>()); Console.WriteLine(db.Addresses.Select(add => new AddressDto { Id = add.Id, Code = add.Code, Title = add.Title, UserId = add.UserId, UserName = add.User.Name })); } Console.WriteLine($"Normal Select {DateTimeOffset.UtcNow}"); using (AppDbContext db = new AppDbContext()) { db.Database.ExecuteSqlCommand("DBCC DROPCLEANBUFFERS"); Stopwatch watch = Stopwatch.StartNew(); List<AddressDto> addresses = db.Addresses.AsNoTracking().Select(add => new AddressDto { Id = add.Id, Code = add.Code, Title = add.Title, UserId = add.UserId, UserName = add.User.Name }).ToList(); List<AddressDto> addresses2 = db.Addresses.AsNoTracking().Select(add => new AddressDto { Id = add.Id, Code = add.Code, Title = add.Title, UserId = add.UserId, UserName = add.User.Name }).ToList(); watch.Stop(); Console.WriteLine($"{watch.ElapsedMilliseconds} {addresses.Count} {addresses2.Count}"); } Console.WriteLine($"AutoMapper Exec {DateTimeOffset.UtcNow}"); using (AppDbContext db = new AppDbContext()) { db.Database.ExecuteSqlCommand("DBCC DROPCLEANBUFFERS"); Stopwatch watch = Stopwatch.StartNew(); List<AddressDto> addresses = db.Addresses.AsNoTracking().ProjectTo<AddressDto>().ToList(); List<AddressDto> addresses2 = db.Addresses.AsNoTracking().ProjectTo<AddressDto>().ToList(); watch.Stop(); Console.WriteLine($"{watch.ElapsedMilliseconds} {addresses.Count} {addresses2.Count}"); } Console.ReadKey(); } } }
حال نتایج بدست آمده، در قسمت پایینتر آن نمایان میشود:
البته نتیجهی این آزمایش بسته به سخت افزار سیستم شما ممکن است کمی متفاوت باشد.
در سه آزمایش دیگر به صورت متوالی نتیجهی زیر بدست آمد:
Normal | AutoMapper |
2451 | 2378 |
2120 | 2111 |
2202 | 2124 |
اگر این مقدار جزئی از تفاوت بین دو نوع مختلف آزمایش را مورد نظر نگیریم، میتوان گفت که هر دو روش نتیجهی کاملا یکسانی خواهند داشت. فقط با استفاده از AutoMapper کدهای کمتری نوشته شدهاست!
اما دلیل چیست؟ از آنجایی که ProjectTo از Dto به Model انجام شده و Lambda Expressionی که به سمت Entity Framework فرستاده شدهاست با روش Normal کاملا برابر است و بقیهی عملیات توسط EF انجام میشود، با قاطعیت میتوان گفت که هر دو روش ذکر شده از نظر Performance کاملا یکسان خواهند بود.
نکته: البته به این موضوع باید توجه شود که اگر همین آزمایش را بطور مثال با استفاده از یک Listی از رکوردهای درون Memory ساخته شده توسط خودمان انجام دهیم، آن موقع نتیجهی یکسانی نخواهیم داشت، به دلیل اینکه EFی دیگر وجود نخواهد داشت که مسئولیت بازگشت دادهها را بر عهده بگیرد. از آنجائیکه اکثر کارهایی که توقع داریم AutoMapper برای ما انجام دهد، توسط ORM بازگشت داده میشود، پس میتوان گفت نکتهی فوق تقریبا در دنیای واقعی رخ نخواهد داد و باعث مشکل نخواهد شد.
• All .NET application (Console, ASP.NET 4, WinForms, WPF) • Mac and Linux applications (Mono) • UWP (Universal Windows Platform) • ASP.NET Core applications • Can use EF Core in Windows phone and Windows store app
PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer PM> Install-Package Microsoft.EntityFrameworkCore.Tools -Pre PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design
{ "dependencies": { // same as before "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0", "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final", "Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.0" } }
{ "dependencies": { // same as before "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0", "Microsoft.EntityFrameworkCore.Tools": { "version": "1.0.0-preview2-final", "type": "build" }, "Microsoft.EntityFrameworkCore.SqlServer.Design": { "version": "1.0.0", "type": "build" } }, "tools": { // same as before "Microsoft.EntityFrameworkCore.Tools": { "version": "1.0.0-preview2-final", "imports": [ "portable-net45+win8" ] } } }
{ "dependencies": { // same as before "Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.0.0" } }
public void Configure(IApplicationBuilder app, IHostingEnvironment env) { if (env.IsDevelopment()) { app.UseDatabaseErrorPage(); }
namespace Core1RtmEmptyTest.Entities { public class Person { public int PersonId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } }
using Microsoft.EntityFrameworkCore; namespace Core1RtmEmptyTest.Entities { public class ApplicationDbContext : DbContext { public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { } public DbSet<Person> Persons { get; set; } } }
protected DbContext() : this((DbContextOptions) new DbContextOptions<DbContext>()) { } public DbContext([NotNull] DbContextOptions options) { // … }
using Microsoft.EntityFrameworkCore; namespace Core1RtmEmptyTest.Entities { public class ApplicationDbContext : DbContext { public DbSet<Person> Persons { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"... connection string ..."); } } }
{ "ConnectionStrings": { "ApplicationDbContextConnection": "Data Source=(local);Initial Catalog=TestDbCore2016;Integrated Security = true" } }
public class Startup { public IConfigurationRoot Configuration { set; get; } public Startup(IHostingEnvironment env) { var builder = new ConfigurationBuilder() .SetBasePath(env.ContentRootPath) .AddJsonFile("appsettings.json", reloadOnChange: true, optional: false) .AddJsonFile($"appsettings.{env}.json", optional: true); Configuration = builder.Build(); } public void ConfigureServices(IServiceCollection services) { services.AddSingleton<IConfigurationRoot>(provider => { return Configuration; }); services.AddDbContext<ApplicationDbContext>(options => { options.UseSqlServer(Configuration["ConnectionStrings:ApplicationDbContextConnection"]); });
using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; namespace Core1RtmEmptyTest.Entities { public class ApplicationDbContext : DbContext { private readonly IConfigurationRoot _configuration; public ApplicationDbContext(IConfigurationRoot configuration) { _configuration = configuration; } //public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) //{ //} public DbSet<Person> Persons { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(_configuration["ConnectionStrings:ApplicationDbContextConnection"]); } } }
public void ConfigureServices(IServiceCollection services) { services.AddDbContext<ApplicationDbContext>();
using System.Linq; using Core1RtmEmptyTest.Entities; using Microsoft.AspNetCore.Mvc; namespace Core1RtmEmptyTest.Controllers { public class TestDBController : Controller { private readonly ApplicationDbContext _ctx; public TestDBController(ApplicationDbContext ctx) { _ctx = ctx; } public IActionResult Index() { var name = _ctx.Persons.First().FirstName; return Json(new { firstName = name }); } } }
private static string GetScript() { string path = AppDomain.CurrentDomain.BaseDirectory + @"Scripts\script.sql"; var file = new FileInfo(path); string script = file.OpenText().ReadToEnd(); return script; } private static void ExecuteScript() { string script = GetScript(); //split the script on "GO" commands var splitter = new[] {"\r\nGO\r\n"}; string[] commandTexts = script.Split(splitter, StringSplitOptions.RemoveEmptyEntries); foreach (string commandText in commandTexts) { using (var ctx = new DbContext()) { if (!string.IsNullOrEmpty(commandText)) { ctx.Database.ExecuteSqlCommand(commandText); } } } }
facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
insert into facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) values (9, 'Spa', 20, 30, 100000, 800); -- OR insert into facilities values (9, 'Spa', 20, 30, 100000, 800);
context.Facilities.Add(new Facility { Name = "Spa", MemberCost = 20, GuestCost = 30, InitialOutlay = 100000, MonthlyMaintenance = 800 }); context.SaveChanges();
facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800. facid: 10, Name: 'Squash Court 2', membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.
insert into facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) values (9, 'Spa', 20, 30, 100000, 800), (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);
context.Facilities.Add(new Facility { Name = "Spa", MemberCost = 20, GuestCost = 30, InitialOutlay = 100000, MonthlyMaintenance = 800 }); context.Facilities.Add(new Facility { Name = "Squash Court 2", MemberCost = 3.5M, GuestCost = 17.5M, InitialOutlay = 5000, MonthlyMaintenance = 80 }); context.SaveChanges();
Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
namespace EFCorePgExercises.Entities { public class FacilityConfiguration : IEntityTypeConfiguration<Facility> { public void Configure(EntityTypeBuilder<Facility> builder) { builder.HasKey(facility => facility.FacId); builder.Property(facility => facility.FacId).IsRequired().UseIdentityColumn(seed: 0, increment: 1);
CREATE TABLE [dbo].[Facilities]( [FacId] [int] IDENTITY(0,1) NOT NULL, --- ... CONSTRAINT [PK_Facilities] PRIMARY KEY CLUSTERED ( [FacId] ASC );
update facilities set initialoutlay = 10000 where facid = 1;
var facility1 = context.Facilities.Find(1); facility1.InitialOutlay = 10000; context.SaveChanges();
update cd.facilities set membercost = 6, guestcost = 30 where facid in (0,1);
int[] facIds = { 0, 1 }; var tennisCourts = context.Facilities.Where(x => facIds.Contains(x.FacId)).ToList(); foreach (var tennisCourt in tennisCourts) { tennisCourt.MemberCost = 6; tennisCourt.GuestCost = 30; } context.SaveChanges();
update cd.facilities facs set membercost = (select membercost * 1.1 from cd.facilities where facid = 0), guestcost = (select guestcost * 1.1 from cd.facilities where facid = 0) where facs.facid = 1;
var fac0 = context.Facilities.Where(x => x.FacId == 0).First(); var fac1 = context.Facilities.Where(x => x.FacId == 1).First(); fac1.MemberCost = fac0.MemberCost * 1.1M; fac1.GuestCost = fac0.GuestCost * 1.1M; context.SaveChanges();
delete from bookings
context.Bookings.RemoveRange(context.Bookings.ToList()); context.SaveChanges();
delete from members where memid = 37;
var mem37 = context.Members.Where(x => x.MemId == 37).First(); context.Members.Remove(mem37); context.SaveChanges();
var entry = context.Entry(new Member { MemId = 37 }); entry.State = EntityState.Deleted; context.SaveChanges();
SET NOCOUNT ON; DELETE FROM [Members] WHERE [MemId] = @p0; SELECT @@ROWCOUNT;
delete from members where memid not in (select memid from cd.bookings);
var mems = context.Members.Where(x => !context.Bookings.Select(x => x.MemId).Contains(x.MemId)).ToList(); context.Members.RemoveRange(mems); context.SaveChanges();