public async Task <(string, IIntegrationMessage)?> SetFirstWaitingMessageInProgressAsync(CancellationToken cancellationToken = default)
{
var commandText = $@"
DECLARE @Updated table( [Id] nvarchar(255), [OutboxMessageStatus] int, [MessagePayload] nvarchar(max), [Description] nvarchar(max), [CreatedOn] datetime)
UPDATE [dbo].[OutboxMessages] SET OutboxMessageStatus = {(int) OutboxMessageStatus.InProgress}
OUTPUT INSERTED.Id, INSERTED.OutboxMessageStatus, INSERTED.MessagePayload, INSERTED.Description, INSERTED.CreatedOn
INTO @Updated
WHERE Id =
(
SELECT TOP 1 Id
FROM [dbo].[OutboxMessages] WITH (UPDLOCK)
WHERE OutboxMessageStatus = {(int) OutboxMessageStatus.Waiting}
ORDER BY Id
)
SELECT * FROM @Updated
";
var outboxMessages = await _dbContext.Set <OutboxMessage>().FromSqlRaw(commandText)
.ToListAsync(cancellationToken: cancellationToken);
var outboxMessage = outboxMessages.FirstOrDefault();
return(outboxMessage == null
? ((string Id, IIntegrationMessage integrationMessage)?)null
: (outboxMessage.Id, outboxMessage.GetPayload <IIntegrationMessage>()));
}
public static void T5Transaction()
{
using (var cts = new EfDbContext())
{
using (var transaction = cts.Database.BeginTransaction())
{
try
{
var category = new Category
{
CategoryName = "Clothes"
};
cts.Set <Category>().Add(category);
cts.SaveChanges();
throw new Exception("Custom Exception");
var product = new Product
{
ProductName = "Blue Denim Shirt",
CategoryId = category.CategoryId
};
cts.Set <Product>().Add(product);
cts.SaveChanges();
Console.WriteLine("Cateogry and Product both saved");
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Console.WriteLine($"Transaction Roll backed due to some exception:{e.Message}");
throw;
}
}
}
}
public int AddHistoryJob(HistoryJob hj)
{
ctx.Set <HistoryJob>().Add(hj);
ctx.SaveChanges();
int id = hj.IdHistoryJob;
return(id);
}
public int AddProject(Project project)
{
ctx.Set <Project>().Add(project);
ctx.SaveChanges();
int id = project.IdProject;
return(id);
}
public async Task <T> Add(T domainObject)
{
if (_service.Set <T>().Any(x => x.UserId == domainObject.UserId))
{
throw new Exception("User already exist");
}
await _service.Set <T>().AddAsync(domainObject);
await _service.SaveChangesAsync();
return(domainObject);
}
/// <summary>
/// Zwraca true w przypadku jak sie zarejestruje, false w momencie gdy nick jest zajety
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <returns></returns>
public bool RegisterUser(string name, string password)
{
if (!UserExists(name))
{
ctx.Set <User>().Add(new User {
Name = name, Password = password
});
ctx.SaveChanges();
return(true);
}
else
{
return(false);
}
}
public static void TrackingSimple(EfDbContext efContext)
{
efContext.Database.Log = Console.WriteLine;
var watch = new Stopwatch();
watch.Start();
efContext.Set <SimpleClass>().ToList().ForEach(d =>
{
//var pro1 = d.Property1;
//d.Property1 = d.Property2;
//d.Property2 = pro1;
//var prop3 = d.Property3;
//d.Property3 = d.Property4;
//d.Property4 = prop3;
var simple = new SimpleClass
{
Id = d.Id,
Property1 = d.Property1,
Property2 = d.Property2,
Property3 = d.Property3,
Property4 = d.Property4
};
new Program().Clone(simple);
});
efContext.SaveChanges();
watch.Stop();
Console.WriteLine(watch.ElapsedMilliseconds);
}
/// <summary>
/// Thêm một List các object vào database
/// </summary>
/// <param name="entities">List đối tượng</param>
/// <returns></returns>
/// CreatedBy NVMANH (20/05/2020)
public virtual async Task AddRangeAsync(List <T> entities)
{
using var dbContext = new EfDbContext();
await dbContext.Set <T>().AddRangeAsync(entities);
await SaveChangesAsync();
}
//TODO:NVMANH
public virtual async Task <int> CountAsync(ISpecification <T> spec)
{
using var dbContext = new EfDbContext();
var data = SpecificationEvaluator <T> .GetQuery(dbContext.Set <T>().AsQueryable(), spec);
return(await data.CountAsync());
}
/// <summary>
/// Lấy thông tin chi tiết của entity theo khóa chính
/// </summary>
/// <param name="id">Giá trị của khóa chính</param>
/// <returns></returns>
/// CreatedBy: NVMANH (05/2020)
public virtual async Task <T> GetEntityByIdAsync(object id)
{
using EfDbContext dbContex = new EfDbContext();
var type = GetTypeOfPrimaryKey();
var converter = TypeDescriptor.GetConverter(type);
var idAfterConvert = converter.ConvertFromString(id.ToString());
return(await dbContex.Set <T>().FindAsync(idAfterConvert));
}
/// <summary>
/// 变更追踪原理
/// </summary>
/// <param name="efContext"></param>
public static void ChangeTracking(EfDbContext efContext)
{
//当我们将类中所有属性设置为virtual时，上下文中存在的就是POCO而是其派生类，此时实体总是被跟踪
var entity = efContext.Set <Customer>().FirstOrDefault(d => d.Name == "ChenJie");
Console.WriteLine(efContext.Entry(entity).State);
entity.Name = "ChenJie";
Console.WriteLine(efContext.Entry(entity).State);
}
/// <summary>
/// Lấy thông tin File nhập khẩu
/// </summary>
/// <param name="spec">Các tiêu chí lấy ra bản ghi</param>
/// <returns></returns>
/// CreatedBy: NVMANH (06/05/2020)
public async Task <ImportFileTemplate> GetFileImportInfo(ISpecification <ImportFileTemplate> spec)
{
using EfDbContext dbContex = new EfDbContext();
var queryData = SpecificationEvaluator <ImportFileTemplate> .GetQuery(dbContex.Set <ImportFileTemplate>().AsQueryable(), spec);
var importFileInfo = await queryData.Include(e => e.ImportWorksheet).ThenInclude(e => e.ImportColumn).ToListAsync();
//var importFileInfo = (await ApplySpecification(spec).Include(e => e.ImportWorksheet).ThenInclude(e => e.ImportColumn).ToListAsync()).FirstOrDefault();
return(importFileInfo.FirstOrDefault());
}
/// <summary>
/// thêm mới dữ liệu sử dụng EF Core
/// </summary>
/// <param name="entity">Đối tượng thêm mới</param>
/// <returns>Trả về đối tượng vừa được thêm mới</returns>
/// CreatedBy: NVMANH (05/2020)
public virtual async Task <T> AddAsync(T entity)
{
using var dbContext = new EfDbContext();
DoBeforeInsert(entity);
await dbContext.Set <T>().AddAsync(entity);
await SaveChangesAsync();
DoAfterSave(entity);
return(entity);
}
public void ReturnInstanceOfDbBaseSetOfTheCorrectClass_WhenAClassIsProvided()
{
// Arrange
IEfDbContext context = new EfDbContext();
// Act
var actualResult = context.Set <User>();
// Assert
Assert.IsInstanceOf <DbSet <User> >(actualResult);
}
public async Task <User> GetUserAsync(Email email, CancellationToken cancellationToken)
{
var user = await _efDbContext.Set <User>()
.FirstOrDefaultAsync(x => x.Email.Value == email.Value, cancellationToken);
if (user == null)
{
throw new NotFoundException <User>();
}
return(user);
}
/// <summary>
/// Zwraca id nowego zadania
/// </summary>
/// <param name="job"></param>
/// <returns></returns>
public int AddJob(Job job)
{
job.SP = job.SP == 0 ? null : job.SP;
job.IdSprint = sprintRepo.GetLastSprintForProject(job.IdProject).IdSprint;
ctx.Set <Job>().Add(job);
ctx.SaveChanges();
int id = job.IdJob;
//W momencie utworzenia zadania tworzy sie log
HistoryJob hj = new HistoryJob();
hj.FromBacklog = (int)typeBacklog.New;
hj.ToBacklog = (int)typeBacklog.New;
hj.FromTable = (int)typeTable.None;
hj.ToTable = (int)typeTable.None;
hj.IdJob = job.IdJob;
hj.IdUser = job.IdUser;
hj.Date = DateTime.Today;
hj.Comment = "Utworzono zadanie \"" + job.Title + "\" przez użytkownika " + userRepo.Users.SingleOrDefault(U => U.IdUser == job.IdUser).Name;
ctx.SaveChanges();
hjRepo.AddHistoryJob(hj);
return(id);
}
/// <summary>
/// Bez numeru i tytułu, sprintu, wyliczalny
/// </summary>
/// <param name="sprint"></param>
/// <returns>
/// Id sprintu, -1 w przypadku istniejacego otwartego sprintu dla projektu</returns>
public int AddSprint(Project project, DateTime data)
{
//Otwarty sprint
if (GetLastSprintForProject(project) == null)
{
Sprint sprint = new Sprint
{
IdProject = project.IdProject,
EndData = data
};
IEnumerable <Sprint> sprints = GetSprintsForProject(project.IdProject);
sprint.NoSprint = sprints.ToList().Count == 0 ? 1 : sprints.Max(q => q.NoSprint) + 1;
sprint.Title = project.Name + " - Sprint " + sprint.NoSprint;
ctx.Set <Sprint>().Add(sprint);
ctx.SaveChanges();
int id = sprint.IdSprint;
return(id);
}
else
{
return(-1);
}
}
public async Task <IActionResult> Ef()
{
try
{
var cities = await _efContext.Set <EfOpenCity>().AsNoTracking().ToListAsync();
return(Ok(cities));
}
catch (Exception ex)
{
var message = $"Message:{ex.Message} \n $InnerMessage: {ex.InnerException.Message}";
return(BadRequest(message));
}
}
/// <summary>
/// Lấy toàn bộ dữ liệu sử dụng EF core
/// </summary>
/// <returns>List toàn bộ dữ liệu trong bảng</returns>
/// CreatedBy: NVMANH (05/2020)
public virtual async Task <IReadOnlyList <T> > GetListAsync()
{
using EfDbContext dbContex = new EfDbContext();
var entities = await dbContex.Set <T>().ToListAsync();
var firstEntity = entities.FirstOrDefault();
var property = firstEntity.GetType().GetProperty("OrganizationId", BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
// Nếu có thông tin đơn vị thì lấy dữ liệu theo đơn vị:
if (property != null && _organization != null)
{
return(entities.Where(e => Guid.Parse((e.GetType().GetProperty("OrganizationId", BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public).GetValue(e) ?? Guid.Empty).ToString()) == _organization.OrganizationId).ToList());
}
return(entities);
}
/// <summary>
/// Lấy dữ liệu theo các tiêu chí đặt sẵn sử dụng EF core
/// </summary>
/// <returns>List dữ liệu trong bảng</returns>
/// CreatedBy: NVMANH (05/2020
//TODO:NVMANH
public virtual async Task <IReadOnlyList <Y> > GetListAsyncBySpecification <Y>(ISpecification <Y> spec) where Y : BaseEntity
{
using var dbContext = new EfDbContext();
var entities = await(SpecificationEvaluator <Y> .GetQuery(dbContext.Set <Y>().AsQueryable(), spec)).ToListAsync();
var firstEntity = entities.FirstOrDefault();
if (firstEntity == null)
{
return(entities);
}
var property = firstEntity.GetType().GetProperty("OrganizationId", BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
// Nếu có thông tin đơn vị thì lấy dữ liệu theo đơn vị:
if (property != null && _organization != null)
{
return(entities.Where(e => Guid.Parse((e.GetType().GetProperty("OrganizationId", BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public).GetValue(e) ?? Guid.Empty).ToString()) == _organization.OrganizationId).ToList());
}
return(entities);
}
public Repository(EfDbContext context)
{
_context = context;
_dbSet = context.Set <T>();
}
public T Get(int id)
{
return(Context.Set <T>().Find(id));
}
public virtual IQueryable <T> GetAll()
{
return(_context.Set <T>());
}
protected virtual Task <T> GetOneAsync(int id)
{
return(Task.Run(() => _efDbContext.Set <T>().FirstOrDefault(PredicateBuilder.True <T>().And(x => x.Id == id))));
}
public async Task AddAsync(TEntity entity)
{
await _context.Set <TEntity>().AddAsync(entity);
}
public async Task <T> GetByIdAsync(int id)
{
return(await _context.Set <T>().FindAsync(id));
}
public TEntity BuscarPorId(int id)
{
return(_efbdcontext.Set <TEntity>().Find(id));
}
public void Add(T entity)
{
_context.Set <T>().Add(entity);
}
public async Task <bool> ExistsById(long id)
{
return(await _dbContext.Set <TEntity>().AnyAsync(p => p.ID == id));
}
/// <summary>
/// Xóa dữ liệu sử dụng EF Core
/// </summary>
/// <param name="entity"></param>
/// <returns>TRả về đối tượng đã xóa</returns>
/// CreatedBy: NVMANH (04/2020)
public virtual async Task DeleteAsync(T entity)
{
using var dbContext = new EfDbContext();
dbContext.Set <T>().Remove(entity);
await dbContext.SaveChangesAsync();
}