public IEnumerable <dynamic> AddEdo(List <tEDO> data, string UserName)
{
foreach (var e in data)
{
e.InDateTime = DateTime.Now;
e.UserName = UserName.Left(60);
}
db.tEDO.AddRange(data);
db.SaveChanges();
var ids = data.Select(p => p.ID);
var q = from i in db.tEDO.AsNoTracking().Where(p => ids.Contains(p.ID))
select new
{
i.ID,
i.Crypto,
i.DateCancel,
i.EmailTo,
EmailToName = UserDbFunction.ufEmailToStr(i.EmailTo),
i.Department,
i.EDO,
i.FIO_owner,
i.FIO_resp,
i.InDateTime,
i.IsCancel,
i.Publisher,
i.Srok,
i.Storage,
i.FileName,
i.UserName
};
return(q);
}
public IEnumerable <dynamic> AddReestr(List <tReestr> data)
{
foreach (var e in data)
{
e.InDateTime = DateTime.Now;
}
db.tReestr.AddRange(data);
db.SaveChanges();
var ids = data.Select(p => p.ID);
var q = from i in db.tReestr.AsNoTracking().Where(p => ids.Contains(p.ID))
select new
{
i.ID,
i.Nomer,
i.DateR,
i.EmailTo,
EmailToName = UserDbFunction.ufEmailToStr(i.EmailTo),
i.FileName,
i.InstOwnerID,
i.StatusID,
i.Theme,
InstOwner = i.tObjClassifier.NameBrief
};
return(q);
}
public IEnumerable <dynamic> UpdEdo(List <tEDO> data, string UserName)
{
foreach (var e in data.Where(p => p.ID > 0))
{
var q1 = db.tEDO.Find(e.ID);
if (q1 != null)
{
q1.Crypto = e.Crypto;
q1.EmailTo = e.EmailTo;
q1.DateCancel = e.DateCancel;
q1.UserName = UserName;
q1.InDateTime = DateTime.Now;
q1.Department = e.Department;
q1.EDO = e.EDO;
q1.FileName = e.FileName;
q1.FIO_owner = e.FIO_owner;
q1.FIO_resp = e.FIO_resp;
q1.IsCancel = e.IsCancel;
q1.Publisher = e.Publisher;
q1.Srok = e.Srok;
q1.Storage = e.Storage;
db.SaveChanges();
}
}
var ids = data.Select(p => p.ID);
var q = from i in db.tEDO.AsNoTracking().Where(p => ids.Contains(p.ID))
select new
{
i.ID,
i.Crypto,
i.DateCancel,
i.EmailTo,
EmailToName = UserDbFunction.ufEmailToStr(i.EmailTo),
i.Department,
i.EDO,
i.FileName,
i.FIO_owner,
i.FIO_resp,
i.InDateTime,
i.IsCancel,
i.Publisher,
i.Srok,
i.Storage,
i.UserName
};
return(q);
}
private static void Main(string[] args)
{
var dbContext = new SampleDataModel();
//EXAMPLE 1: use sql scalar user defined function in projection
var userProfiles = dbContext.Users.Select(x => new { EmailAddress = x.EmailAddress, FullName = UserDbFunction.FnUserFullName(x.Userid) }).Take(3).ToList();
Console.WriteLine("Top 3 users:");
foreach (var userProfile in userProfiles)
{
Console.WriteLine($"Email Address: {userProfile.EmailAddress} - Full Name: {userProfile.FullName}");
}
//EXAMPLE 2: use sql scalar user defined function in restriction (where clause)
//Find 'Barbara Santa' email address
var user = dbContext.Users.FirstOrDefault(x => UserDbFunction.FnUserFullName(x.Userid).Equals("Barbara Santa", StringComparison.OrdinalIgnoreCase));
Console.WriteLine(Environment.NewLine + $"'Barbara Santa' email address: {user?.EmailAddress}");
Console.ReadLine();
}
public IEnumerable <dynamic> GetEdoList(string sort, string dir, DateTime?d1, DateTime?d2)
{
var q1 = db.tEDO.Where(p => 1 == 1);
if (d1.HasValue)
{
q1 = q1.Where(a => a.Srok >= d1);
}
if (d2.HasValue)
{
q1 = q1.Where(a => a.Srok <= d2);
}
var q = from i in q1
select new
{
i.ID,
i.Crypto,
i.DateCancel,
i.EmailTo,
EmailToName = UserDbFunction.ufEmailToStr(i.EmailTo),
i.Department,
i.EDO,
i.FIO_owner,
i.FIO_resp,
i.InDateTime,
i.IsCancel,
i.Publisher,
i.Srok,
i.Storage,
i.FileName,
i.UserName
};
if (sort != null)
{
q = q.OrderBy(sort + (dir == "DESC" ? " descending" : ""));
}
return(q);
}
public IEnumerable <dynamic> GetReestrList(string sort, string dir, DateTime?d1, DateTime?d2, int?InstOwnerID)
{
var q1 = db.tReestr.Where(p => 1 == 1);
if (InstOwnerID.HasValue)
{
q1 = q1.Where(p => p.InstOwnerID == InstOwnerID);
}
var q = from i in q1
select new
{
i.ID,
i.Nomer,
i.DateR,
i.EmailTo,
EmailToName = UserDbFunction.ufEmailToStr(i.EmailTo),
i.FileName,
i.InstOwnerID,
i.StatusID,
i.Theme,
InstOwner = i.tObjClassifier.NameBrief
};
if (d1.HasValue)
{
q = q.Where(a => a.DateR >= d1);
}
if (d2.HasValue)
{
q = q.Where(a => a.DateR <= d2);
}
if (sort != null)
{
q = q.OrderBy(sort + (dir == "DESC" ? " descending" : ""));
}
return(q);
}
public IEnumerable <dynamic> UpdReestr(List <tReestr> data)
{
foreach (var e in data.Where(p => p.ID > 0))
{
var q1 = db.tReestr.Find(e.ID);
if (q1 != null)
{
q1.DateR = e.DateR;
q1.EmailTo = e.EmailTo;
q1.FileName = e.FileName;
q1.InDateTime = DateTime.Now;
q1.InstOwnerID = e.InstOwnerID;
q1.Nomer = e.Nomer;
q1.StatusID = e.StatusID;
q1.Theme = e.Theme;
db.SaveChanges();
}
}
var ids = data.Select(p => p.ID);
var q = from i in db.tReestr.AsNoTracking().Where(p => ids.Contains(p.ID))
select new
{
i.ID,
i.Nomer,
i.DateR,
i.EmailTo,
EmailToName = UserDbFunction.ufEmailToStr(i.EmailTo),
i.FileName,
i.InstOwnerID,
i.StatusID,
i.Theme,
InstOwner = i.tObjClassifier.NameBrief
};
return(q);
}
