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); }