// Use it to import from resource - look at ImportDefaultSecretQuestions
public static int ImportSecretQuestions(IEntitySession session, Stream stream)
{
var reader = new StreamReader(stream);
var text = reader.ReadToEnd(); // File.ReadAllLines(filePath);
var lines = text.Split(new [] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
// Postgres blows up here, trying with LINQ
//var oldList = session.GetEntities<ISecretQuestion>();
var oldList = session.EntitySet <ISecretQuestion>().ToList();
var count = 0;
foreach (var line in lines)
{
if (string.IsNullOrWhiteSpace(line) || line.StartsWith("//"))
{
continue;
}
//check if there's existing
var trimLine = line.Trim();
var oldLine = oldList.FirstOrDefault(l => l.Question == trimLine);
if (oldLine != null)
{
continue;
}
var q = session.NewEntity <ISecretQuestion>();
q.Question = trimLine;
q.Number = count++;
q.Flags = SecretQuestionFlags.None;
}
return(count);
}
} // method
private void CreateNextRunsForScheduledRuns(IEntitySession session, IList <IJobRun> jobRuns, DateTime utcNow)
{
//Find all schedules involed with these jobs/runs
var jobIds = jobRuns.Select(j => j.Job.Id).ToList();
var scheds = session.EntitySet <IJobSchedule>().Where(js => jobIds.Contains(js.Job.Id)).ToList();
if (scheds.Count == 0)
{
return;
}
foreach (var sched in scheds)
{
var nextStartOn = sched.GetNextStartAfter(utcNow);
if (nextStartOn != null)
{
var nextRun = NewJobRun(sched.Job, JobRunType.Schedule, nextStartOn);
sched.NextRunId = nextRun.Id;
}
else
{
sched.NextRunId = null;
sched.Status = JobScheduleStatus.Stopped;
}
} //foreach
} //method
public IOAuthClientFlow GetOAuthFlow(IEntitySession session, Guid flowId)
{
// Limit to logged in user, to avoid authorizatio exception
var query = session.EntitySet <IOAuthClientFlow>().Where(f => f.Id == flowId);
var user = session.Context.User;
if (user.Kind == UserKind.AuthenticatedUser)
{
query = query.Where(f => f.UserId == user.UserId);
}
var flow = query.FirstOrDefault();
if (flow == null)
{
return(null);
}
if (flow.UserId != null)
{
var currUserId = session.Context.User.UserId;
if (currUserId != flow.UserId.Value)
{
return(null);
}
}
CheckExpired(flow);
return(flow);
}
//Low-level methods
public ILogin FindLogin(IEntitySession session, string userName, string password, Guid?tenantId)
{
var context = session.Context;
context.ValidateNotEmpty(userName, ClientFaultCodes.ValueMissing, "UserName", null, "UserName may not be empty");
context.ValidateNotEmpty(password, ClientFaultCodes.ValueMissing, "Password", null, "Password may not be empty");
context.ThrowValidation();
userName = CheckUserName(context, userName);
var userNameHash = Util.StableHash(userName);
var weakPwdHash = GetWeakPasswordHash(password);
var tenantIdValue = tenantId == null ? Guid.Empty : tenantId.Value;
// Note: we do not compare usernames, only UserNameHash values; UserName might be null if we don't save them
var qryLogins = from lg in session.EntitySet <ILogin>()
where lg.UserNameHash == userNameHash && lg.WeakPasswordHash == weakPwdHash &&
lg.TenantId == tenantIdValue
select lg;
//Query logins table
using (session.WithElevateRead()) {
var logins = qryLogins.ToList(); //these are candidates, but most often will be just one
var login = logins.FirstOrDefault(lg => VerifyPassword(lg, password));
if (login != null)
{
VerifyExpirationSuspensionDates(login);
}
return(login);
}
}
public IList <DataHistoryEntry> GetEntityHistory(IEntitySession session, Type entityType, object primaryKey,
DateTime?fromDate = null, DateTime?tillDate = null, int skip = 0, int take = 100, Guid?userId = null)
{
var entInfo = session.Context.App.Model.GetEntityInfo(entityType);
Util.Check(entInfo != null, "Type {0} is not registered as an entity.", entityType);
var entName = entInfo.FullName;
var entNameHash = Util.StableHash(entName);
var where = session.NewPredicate <IDataHistory>()
.And(h => h.EntityNameHash == entNameHash && h.EntityName == entName)
.AndIfNotEmpty(fromDate, h => h.CreatedOn > fromDate.Value)
.AndIfNotEmpty(tillDate, h => h.CreatedOn < tillDate.Value);
if (primaryKey != null)
{
var pkStr = PrimaryKeyAsString(primaryKey);
var pkHash = Util.StableHash(pkStr);
where = where.And(h => h.EntityPrimaryKeyHash == pkHash && h.EntityPrimaryKey == pkStr);
}
var query = session.EntitySet <IDataHistory>().Where(where).OrderByDescending(h => h.CreatedOn).Skip(skip).Take(take);
var histEntList = query.ToList();
var result = histEntList.Select(h => h.ToHistoryEntry(entInfo)).ToList();
return(result);
}
public ILoginProcess GetActiveProcess(IEntitySession session, LoginProcessType processType, string token)
{
Util.Check(!string.IsNullOrWhiteSpace(token), "Process token may not be null");
var context = session.Context;
//get process without expiration checking, we check it later
var query = from p in session.EntitySet <ILoginProcess>()
where p.ProcessType == processType && p.Token == token
select p;
var process = query.FirstOrDefault();
context.ThrowIfNull(process, ClientFaultCodes.ObjectNotFound, "Process", "Login process not found.");
if (process.Status != LoginProcessStatus.Active)
{
return(null);
}
if (process.FailCount >= _settings.MaxFailCount)
{
process.Status = LoginProcessStatus.AbortedAsFraud;
OnLoginEvent(context, LoginEventType.ProcessAborted, process.Login, "Aborted - too many failures.");
session.SaveChanges();
return(null);
}
var userName = process.Login.UserName;
if (process.ExpiresOn < App.TimeService.UtcNow)
{
process.Status = LoginProcessStatus.Expired;
OnLoginEvent(context, LoginEventType.ProcessAborted, process.Login, "Expired.");
session.SaveChanges();
return(null);
}
return(process);
}
internal static void UpdateStatus(this IEntitySession session, Guid tokenId, OAuthTokenStatus status)
{
var updQuery = from t in session.EntitySet <IOAuthAccessToken>()
where t.Id == tokenId
select new { Id = t.Id, Status = status };
var cnt = updQuery.ExecuteUpdate <IOAuthAccessToken>();
}
/// <summary>Returns the last finished (non-Pending) job run for a given job.</summary>
/// <param name="session">Entity session.</param>
/// <param name="jobId">Job ID.</param>
/// <returns>Job run entity if found, null otherwise.</returns>
public static IJobRun GetLastFinishedJobRun(this IEntitySession session, Guid jobId)
{
var jobRun = session.EntitySet <IJobRun>().Where(jr => jr.Job.Id == jobId)
.Where(jr => jr.Status != JobRunStatus.Pending)
.OrderByDescending(jr => jr.AttemptNumber).FirstOrDefault();
return(jobRun);
}
public ILoginExtraFactor FindLoginExtraFactor(IEntitySession session, ExtraFactorTypes factorType, string factor)
{
var entFactor = session.EntitySet <ILoginExtraFactor>()
.FirstOrDefault(ef => ef.FactorType == factorType && ef.FactorValue == factor);
VerifyExpirationSuspensionDates(entFactor.Login);
return(entFactor);
}
// Can be called explicitly outside of migrations to unencrypt existing values
public static void UnencryptFactorValues(IEntitySession session)
{
var loginConfig = session.Context.App.GetConfig <LoginModuleSettings>();
var errLog = session.Context.App.ErrorLog;
int batchSize = 200;
int skip = 0;
try {
var factors = session.EntitySet <ILoginExtraFactor>()
.Where(f => f.FactorValue == null).OrderBy(f => f.CreatedOn)
.Skip(skip).Take(batchSize).ToList();
skip += batchSize;
if (factors.Count == 0)
{
return;
}
//preload all EncryptedValue records
var encrIds = factors.Select(f => f.Info_Id).ToList();
var encrRecs = session.EntitySet <IEncryptedData>().Where(ed => encrIds.Contains(ed.Id));
foreach (var f in factors)
{
if (f.Info_Id == null || f.Info_Id.Value == Guid.Empty)
{
continue;
}
var ed = session.GetEntity <IEncryptedData>(f.Info_Id); //should be preloaded
if (ed == null)
{
continue;
}
f.FactorValue = ed.DecryptString(loginConfig.EncryptionChannelName);
f.Info_Id = null;
}
session.SaveChanges();
} catch (Exception ex) {
if (errLog != null)
{
errLog.LogError(ex, session.Context);
}
if (!SuppressMigrationErrors)
{
throw;
}
}
}
public static ICoupon LookupCoupon(this IEntitySession session, string code)
{
var query = from c in session.EntitySet <ICoupon>()
where c.PromoCode == code
select c;
var coupon = query.FirstOrDefault();
return(coupon);
}
public ITextTemplate GetTemplate(IEntitySession session, string templateName, string culture = "EN-US", Guid? ownerId = null)
{
var where = session.NewPredicate<ITextTemplate>()
.And(t => t.Name == templateName)
.AndIfNotEmpty(culture, t => t.Culture == culture)
.AndIfNotEmpty(ownerId, t => t.OwnerId == ownerId.Value);
var template = session.EntitySet<ITextTemplate>().Where(where).FirstOrDefault();
return template;
}
public static IQueryable <TEntity> EntitySet <TEntity>(this IEntitySession session, LockType lockType) where TEntity : class
{
if (lockType == LockType.None)
{
return(session.EntitySet <TEntity>());
}
var entSession = (EntitySession)session;
return(entSession.CreateEntitySet <TEntity>(lockType));
}
public static IQueryable <TEntity> EntitySet <TEntity>(this IEntitySession session, LockOptions options) where TEntity : class
{
if (options == LockOptions.None)
{
return(session.EntitySet <TEntity>());
}
var entSession = (EntitySession)session;
return(entSession.CreateEntitySet <TEntity>(options));
}
public ITextTemplate GetTemplate(IEntitySession session, string templateName, string culture = "EN-US", Guid?ownerId = null)
{
var where = session.NewPredicate <ITextTemplate>()
.And(t => t.Name == templateName)
.AndIfNotEmpty(culture, t => t.Culture == culture)
.AndIfNotEmpty(ownerId, t => t.OwnerId == ownerId.Value);
var template = session.EntitySet <ITextTemplate>().Where(where).FirstOrDefault();
return(template);
}
public IList <ISecretQuestion> GetAllSecretQuestions(IEntitySession session)
{
var mask = SecretQuestionFlags.Disabled | SecretQuestionFlags.Private;
var query = from q in session.EntitySet <ISecretQuestion>()
where (q.Flags & mask) == 0 //we look for public and enabled
orderby q.Number
select q;
var questions = query.ToList();
return(questions);
}
//Alice defines a utility method to login users.
public static ILogin Login(IEntitySession session, string userName,
int passwordHash)
{
// For simplicity, we use direct LINQ
var query = from login in session.EntitySet<ILogin>()
where login.UserName == userName &&
login.PasswordHash == passwordHash
select login;
var logins = query.ToList();
return logins.Count == 0 ? null : logins[0];
}
public void SaveObjects(IEntitySession session, IList <object> items)
{
//Schedule update query that will update all LastActiveOn columns for all sessions with ID in the hashset
//We could use DateTime.Now as value for LastUsedOn for all sessions, it is within few seconds, but...
// this causes problems for unit tests for session expiration, when we try to shift current time and see if session is expired;
// (with current sessions would be updated to shifted time). So we keep actual time (latest) while we accumulate sessions for update
var query = session.EntitySet <TUserSession>().Where(s => _sessionIds.Contains(s.Id))
.Select(s => new { Id = s.Id, LastUsedOn = _lastUsed });
session.ScheduleNonQuery <TUserSession>(query, Vita.Entities.Linq.LinqCommandType.Update);
}
//Alice defines a utility method to login users.
public static ILogin Login(IEntitySession session, string userName,
int passwordHash)
{
// For simplicity, we use direct LINQ
var query = from login in session.EntitySet <ILogin>()
where login.UserName == userName &&
login.PasswordHash == passwordHash
select login;
var logins = query.ToList();
return(logins.Count == 0 ? null : logins[0]);
}
public static IOAuthRemoteServerAccount GetOAuthAccount(this IEntitySession session, string serverName, string accountName = null)
{
if (accountName == null)
{
var stt = session.GetOAuthSettings();
accountName = stt.DefaultAccountName;
}
var accountQuery = session.EntitySet <IOAuthRemoteServerAccount>().Where(a => a.Server.Name == serverName && a.Name == accountName);
var act = accountQuery.FirstOrDefault();
return(act);
}
private IBookOrder GetOpenOrder(IEntitySession session, LockType lockType, bool create = false)
{
var currUserId = Context.User.UserId;
var openOrder = session.EntitySet <IBookOrder>(lockType)
.Where(bo => bo.User.Id == currUserId && bo.Status == OrderStatus.Open).FirstOrDefault();
if (openOrder == null && create)
{
var user = session.GetEntity <IUser>(Context.User.UserId);
openOrder = session.NewOrder(user);
}
return(openOrder);
}
public DataHistoryEntry GetEntityOnDate(IEntitySession session, Type entityType, object primaryKey, DateTime onDate)
{
Util.Check(primaryKey != null, "Primary key may not be null");
var entInfo = session.Context.App.Model.GetEntityInfo(entityType);
Util.Check(entInfo != null, "Type {0} is not registered as an entity.", entityType);
var entName = entInfo.FullName;
var entNameHash = Util.StableHash(entName);
string pkStr = PrimaryKeyAsString(primaryKey);
var pkHash = Util.StableHash(pkStr);
var query = session.EntitySet<IDataHistory>().Where(h => h.EntityNameHash == entNameHash && h.EntityName == entName &&
h.EntityPrimaryKeyHash == pkHash && h.EntityPrimaryKey == pkStr && h.CreatedOn < onDate)
.OrderByDescending(h => h.CreatedOn);
var histEnt = query.FirstOrDefault();
var result = histEnt.ToHistoryEntry(entInfo);
return result;
}
public IOAuthAccessToken GetUserOAuthToken(IEntitySession session, Guid userId, string serverName, string accountName = null)
{
accountName = accountName ?? Settings.DefaultAccountName;
var context = session.Context;
var utcNow = context.App.TimeService.UtcNow;
var accessToken = session.EntitySet <IOAuthAccessToken>()
.Where(t => t.Account.Server.Name == serverName && t.UserId == userId && t.Status == OAuthTokenStatus.Active)
.OrderByDescending(t => t.RetrievedOn).FirstOrDefault();
if (accessToken != null && accessToken.ExpiresOn < utcNow)
{
session.UpdateStatus(accessToken.Id, OAuthTokenStatus.Expired); //update directly in db
return(null);
}
return(accessToken);
}
/// <summary>
/// Creates a search query expression from a predicate and a <c>SearchParams</c> instance, with optional property mapping for ORDER BY clause.
/// </summary>
/// <typeparam name="T">Root entity type to be searched.</typeparam>
/// <param name="session">Entity session.</param>
/// <param name="where">Search condition.</param>
/// <param name="searchParams">Search options to be added to the result query: Order-By, Skip, Take. </param>
/// <param name="nameMapping">Optional, mapping of names in OrderBy list (in searchParams) to actual entity property names. </param>
/// <param name="options">Optional, search query options.</param>
/// <returns>Combined search query expression.</returns>
public static IQueryable <T> CreateSearch <T>(this IEntitySession session,
Expression <Func <T, bool> > where,
SearchParams searchParams,
Dictionary <string, string> nameMapping = null,
QueryOptions options = QueryOptions.ForceIgnoreCase) where T : class
{
var entQuery = session.EntitySet <T>().Where(where).WithOptions(options);
if (!string.IsNullOrEmpty(searchParams.OrderBy))
{
entQuery = entQuery.OrderBy(searchParams.OrderBy, nameMapping);
}
// Add Skip, Take
var result = entQuery.Skip(searchParams.Skip).Take(searchParams.Take);
return(result);
}
private void SaveModulesInfo(IEntitySession session, DbVersionInfo dbVersion)
{
// important - should use EntitySet here; otherwise MySql fails
var moduleRecs = session.EntitySet <IDbModuleInfo>().ToList();
foreach (var mi in dbVersion.Modules)
{
var mrec = moduleRecs.FirstOrDefault(r => r.ModuleName == mi.ModuleName && r.Schema == mi.Schema);
if (mrec == null)
{
mrec = session.NewEntity <IDbModuleInfo>();
mrec.ModuleName = mi.ModuleName;
mrec.Schema = mi.Schema;
}
mrec.Version = mi.Version.ToString();
}
}
public DataHistoryEntry GetEntityOnDate(IEntitySession session, Type entityType, object primaryKey, DateTime onDate)
{
Util.Check(primaryKey != null, "Primary key may not be null");
var entInfo = session.Context.App.Model.GetEntityInfo(entityType);
Util.Check(entInfo != null, "Type {0} is not registered as an entity.", entityType);
var entName = entInfo.FullName;
var entNameHash = Util.StableHash(entName);
string pkStr = PrimaryKeyAsString(primaryKey);
var pkHash = Util.StableHash(pkStr);
var query = session.EntitySet <IDataHistory>().Where(h => h.EntityNameHash == entNameHash && h.EntityName == entName &&
h.EntityPrimaryKeyHash == pkHash && h.EntityPrimaryKey == pkStr && h.CreatedOn < onDate)
.OrderByDescending(h => h.CreatedOn);
var histEnt = query.FirstOrDefault();
var result = histEnt.ToHistoryEntry(entInfo);
return(result);
}
public static IOAuthRemoteServer CreateOrUpdateServer(IEntitySession session, string name, OAuthServerOptions options,
string siteUrl, string authorizationUrl, string tokenRequestUrl, string tokenRefreshUrl, string scopes,
string documentationUrl, string basicProfileUrl, string profileUserIdTag)
{
IOAuthRemoteServer srv = session.EntitySet<IOAuthRemoteServer>().Where(s => s.Name == name).FirstOrDefault();
if(srv == null)
srv = session.NewEntity<IOAuthRemoteServer>();
srv.Name = name;
srv.Options = options;
srv.SiteUrl = siteUrl;
srv.AuthorizationUrl = authorizationUrl;
srv.TokenRequestUrl = tokenRequestUrl;
srv.TokenRefreshUrl = tokenRefreshUrl;
srv.Scopes = scopes;
srv.DocumentationUrl = documentationUrl;
srv.BasicProfileUrl = basicProfileUrl;
srv.ProfileUserIdTag = profileUserIdTag;
return srv;
}
/// <summary>Returns a job entity by unique name. </summary>
/// <param name="session">Entity session.</param>
/// <param name="jobName">Job name.</param>
/// <returns>Job entity if found.</returns>
/// <remarks>Job names does not have to be unique in the database. However, you may create specific reusable jobs with unique names
/// and lookup them up by name. This method throws exception if a job is not found, or if it finds more than one match.</remarks>
public static IJob GetJobByUniqueName(this IEntitySession session, string jobName)
{
var jobs = session.EntitySet <IJob>().Where(j => j.Name == jobName).Take(2).ToList();
switch (jobs.Count)
{
case 0:
Util.Check(false, "Job {0} not found.");
break;
case 1:
return(jobs[0]);
default:
Util.Check(false, "More than one job found with name '{0}'.", jobName);
break;
}
return(null); //never happens
}
public ILogin GetLogin(IEntitySession session)
{
var context = session.Context;
var user = context.User;
context.ThrowIf(user.Kind != UserKind.AuthenticatedUser, ClientFaultCodes.InvalidValue, "User", "User must be authenticated.");
var query = session.EntitySet <ILogin>();
if (user.UserId == Guid.Empty)
{
query = query.Where(lg => lg.AltUserId == user.AltUserId);
}
else
{
query = query.Where(lg => lg.UserId == user.UserId);
}
var login = query.FirstOrDefault();
return(login);
}
/// <summary>Returns a list of entities.</summary>
/// <typeparam name="TEntity">Entity type.</typeparam>
/// <param name="session">Entity session.</param>
/// <param name="skip">Optional. A number of entities to skip.</param>
/// <param name="take">Maximum number of entities to include in results.</param>
/// <param name="orderBy">Order by expression.</param>
/// <param name="descending">Descening order flag.</param>
/// <returns>A list of entities.</returns>
public static IList <TEntity> GetEntities <TEntity>(this IEntitySession session,
Expression <Func <TEntity, object> > orderBy = null, bool descending = false,
int?skip = null, int?take = null) where TEntity : class
{
var query = session.EntitySet <TEntity>();
if (orderBy != null)
{
query = descending ? query.OrderByDescending(orderBy) : query.OrderBy(orderBy);
}
if (skip != null)
{
query = query.Skip(skip.Value);
}
if (take != null)
{
query = query.Take(take.Value);
}
return(query.ToList());
}
//Low-level methods
public ILogin FindLogin(IEntitySession session, string userName, string password, Guid?tenantId)
{
var context = session.Context;
context.ValidateNotEmpty(userName, ClientFaultCodes.ValueMissing, "UserName", null, "UserName may not be empty");
context.ValidateNotEmpty(password, ClientFaultCodes.ValueMissing, "Password", null, "Password may not be empty");
context.ThrowValidation();
userName = CheckUserName(context, userName);
var userNameHash = _hashService.ComputeHash(userName);
var tenantIdValue = tenantId == null ? Guid.Empty : tenantId.Value;
var qryLogins = from lg in session.EntitySet <ILogin>()
where lg.UserNameHash == userNameHash && lg.UserName == userName
select lg;
// Match password
using (session.WithElevatedRead()) {
var login = qryLogins.FirstOrDefault();
return(login);
}
}
public IList<DataHistoryEntry> GetEntityHistory(IEntitySession session, Type entityType, object primaryKey,
DateTime? fromDate = null, DateTime? tillDate = null, int skip = 0, int take = 100, Guid? userId = null)
{
var entInfo = session.Context.App.Model.GetEntityInfo(entityType);
Util.Check(entInfo != null, "Type {0} is not registered as an entity.", entityType);
var entName = entInfo.FullName;
var entNameHash = Util.StableHash(entName);
var where = session.NewPredicate<IDataHistory>()
.And(h => h.EntityNameHash == entNameHash && h.EntityName == entName)
.AndIfNotEmpty(fromDate, h => h.CreatedOn > fromDate.Value)
.AndIfNotEmpty(tillDate, h => h.CreatedOn < tillDate.Value);
if (primaryKey != null) {
var pkStr = PrimaryKeyAsString(primaryKey);
var pkHash = Util.StableHash(pkStr);
where = where.And(h => h.EntityPrimaryKeyHash == pkHash && h.EntityPrimaryKey == pkStr);
}
var query = session.EntitySet<IDataHistory>().Where(where).OrderByDescending(h => h.CreatedOn).Skip(skip).Take(take);
var histEntList = query.ToList();
var result = histEntList.Select(h => h.ToHistoryEntry(entInfo)).ToList();
return result;
}
/// <summary>Executes a search query for a given WHERE condition, and returns results object which contains the resulting rows (page)
/// and total count of rows for a search condition.</summary>
/// <typeparam name="TEntity">Root entity type.</typeparam>
/// <param name="session">Entity session.</param>
/// <param name="where">Combined WHERE condition for a query.</param>
/// <param name="searchParams">Search parameters object containing extra options for the query: order by, skip, take.</param>
/// <param name="include">Include expression.</param>
/// <param name="nameMapping">A name mapping dictionary, to map names in order-by expression to actual properties of the entity.</param>
/// <param name="options">Optional, search query options.</param>
/// <returns>An instance of the <c>SearchResults</c> class, with selected rows and total count for the query condition.</returns>
public static SearchResults <TEntity> ExecuteSearch <TEntity>(
this IEntitySession session,
Expression <Func <TEntity, bool> > where,
SearchParams searchParams,
Expression <Func <TEntity, object> > include = null,
Dictionary <string, string> nameMapping = null,
QueryOptions options = QueryOptions.ForceIgnoreCase) where TEntity : class
{
var baseQuery = session.EntitySet <TEntity>().Where(where).WithOptions(options); //this will be reused by Count query
//add include, order by, skip, take
var incQuery = include == null ? baseQuery : baseQuery.Include <TEntity>(include);
var orderedQuery = string.IsNullOrEmpty(searchParams.OrderBy) ? incQuery : incQuery.OrderBy(searchParams.OrderBy, nameMapping);
// Add Skip, Take; we return '+1' rows, to check if there are any more; if not, we do not need to query total
var takePlusOne = searchParams.Take + 1;
var pageQuery = orderedQuery.Skip(searchParams.Skip).Take(takePlusOne);
var rows = pageQuery.ToList();
if (rows.Count < takePlusOne)
{
//We see the last row, we do not need to run total query
return new SearchResults <TEntity>()
{
Results = rows, TotalCount = searchParams.Skip + rows.Count
}
}
;
// we received more than Take number of rows; it means we need to run TotalCount
//save main query command, and restore it after total query; in debugging main query is more interesting than total query
var queryCmd = session.GetLastCommand();
var totalCount = baseQuery.Count(); //use baseQuery here, without OrderBy, Skip, Take
session.SetLastCommand(queryCmd); //restore main query command
rows.RemoveAt(rows.Count - 1); //remove last extra row
var results = new SearchResults <TEntity>()
{
Results = rows, TotalCount = totalCount
};
return(results);
}
public static SearchResults <Book> SearchBooks(this IEntitySession session, BookSearch searchParams)
{
// Warning about substring match (LIKE): Be careful using it in real apps, against big tables
// Match by fragment results in LIKE operator which NEVER works on real volumes.
// For MS SQL, it is OK to do LIKE with pattern that does not start with % (so it is StartsWith(smth) operator).
// AND column must be indexed - so server will use index. For match inside the string, LIKE is useless on big tables.
// In our case, Title is indexed and we use StartsWith, so it's OK
// An interesting article about speeding up string-match search in MS SQL:
// http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server/
var categories = ConvertHelper.ParseEnumArray <BookCategory>(searchParams.Categories);
var where = session.NewPredicate <IBook>()
.AndIfNotEmpty(searchParams.Title, b => b.Title.StartsWith(searchParams.Title))
.AndIfNotEmpty(searchParams.MaxPrice, b => b.Price <= (Decimal)searchParams.MaxPrice.Value)
.AndIfNotEmpty(searchParams.Publisher, b => b.Publisher.Name.StartsWith(searchParams.Publisher))
.AndIfNotEmpty(searchParams.PublishedAfter, b => b.PublishedOn.Value >= searchParams.PublishedAfter.Value)
.AndIfNotEmpty(searchParams.PublishedBefore, b => b.PublishedOn.Value <= searchParams.PublishedBefore.Value)
.AndIf(categories != null && categories.Length > 0, b => categories.Contains(b.Category));
// A bit more complex clause for Author - it is many2many, results in subquery
if (!string.IsNullOrEmpty(searchParams.AuthorLastName))
{
var qAuthBookIds = session.EntitySet <IBookAuthor>()
.Where(ba => ba.Author.LastName.StartsWith(searchParams.AuthorLastName))
.Select(ba => ba.Book.Id);
where = where.And(b => qAuthBookIds.Contains(b.Id));
}
// Alternative method for author name - results in inefficient query (with subquery for every row)
// if(!string.IsNullOrEmpty(authorLastName))
// where = where.And(b => b.Authors.Any(a => a.LastName == authorLastName));
//Use VITA-defined helper method ExecuteSearch - to build query from where predicate, get total count,
// add clauses for OrderBy, Take, Skip, run query and convert to list of model objects with TotalCount
var results = session.ExecuteSearch(where, searchParams, ibook => ibook.ToModel(), b => b.Publisher,
nameMapping: _orderByMapping);
return(results);
}
//For now implemented using dynamically built LINQ query; stored proc support to come later
// TODO: save filter Func in EntityInfo and reuse it
public static TEntity GetEntity <TEntity>(this IEntitySession session, object primaryKey, LockOptions options)
where TEntity : class
{
if (options == LockOptions.None)
{
return(session.GetEntity <TEntity>(primaryKey)); //short path, no locks
}
session.LogMessage("-- Locking entity {0}/{1}", typeof(TEntity).Name, primaryKey);
var entInfo = session.Context.App.Model.GetEntityInfo(typeof(TEntity), throwIfNotFound: true);
var pkMembers = entInfo.PrimaryKey.KeyMembers;
Util.Check(pkMembers.Count == 1, "Cannot lock entity {0}: composite primary keys not supported.", entInfo.Name);
var pkMember = entInfo.PrimaryKey.KeyMembers[0].Member;
var prmEnt = Expression.Parameter(typeof(TEntity), "e");
var pkRead = Expression.MakeMemberAccess(prmEnt, pkMember.ClrMemberInfo);
var eq = Expression.Equal(pkRead, Expression.Constant(primaryKey, pkMember.DataType));
var filter = Expression.Lambda <Func <TEntity, bool> >(eq, prmEnt);
var query = session.EntitySet <TEntity>(options).Where(filter);
// We use ToList() on entire query instead of First() because we have already filter on PK value,
// and we want to avoid adding any paging (skip/take) clauses to the SQL.
// We use FirstOrDefult on entire list, and check that we got something; if not, we throw clear message.
var ent = query.ToList().FirstOrDefault();
Util.Check(ent != null, "Entity {0} with ID {1} does not exist, cannot set the lock.", entInfo.EntityType.Name,
primaryKey);
return(ent);
/*
* //The following is just a sketch
* if (checkLastModifiedId != null) {
* Util.Check(entInfo.VersioningMember != null, "Entity {0} has no tracking/versioning column (last modified transaction id), cannot check data version.", entInfo.Name);
* var lastTransId = EntityHelper.GetProperty<Guid>(ent, entInfo.VersioningMember.MemberName);
* session.Context.ThrowIf(doNotUse_checkLastModifiedId.Value != lastTransId, ClientFaultCodes.ConcurrentUpdate, entInfo.VersioningMember.MemberName, "Entity {0} was modified by concurrent process.", entInfo.Name);
* }
* */
}
//Low-level methods
public ILogin FindLogin(IEntitySession session, string userName, string password, Guid? tenantId)
{
var context = session.Context;
context.ValidateNotEmpty(userName, ClientFaultCodes.ValueMissing, "UserName", null, "UserName may not be empty");
context.ValidateNotEmpty(password, ClientFaultCodes.ValueMissing, "Password", null, "Password may not be empty");
context.ThrowValidation();
userName = CheckUserName(context, userName);
var userNameHash = Util.StableHash(userName);
var weakPwdHash = GetWeakPasswordHash(password);
var tenantIdValue = tenantId == null ? Guid.Empty : tenantId.Value;
// Note: we do not compare usernames, only UserNameHash values; UserName might be null if we don't save them
var qryLogins = from lg in session.EntitySet<ILogin>()
where lg.UserNameHash == userNameHash && lg.WeakPasswordHash == weakPwdHash
&& lg.TenantId == tenantIdValue
select lg;
//Query logins table
using(session.WithElevateRead()) {
var logins = qryLogins.ToList(); //these are candidates, but most often will be just one
var login = logins.FirstOrDefault(lg => VerifyPassword(lg, password));
if(login != null)
VerifyExpirationSuspensionDates(login);
return login;
}
}
private IBookOrder GetOpenOrder(IEntitySession session, LockOptions lockOptions, bool create = false)
{
var currUserId = Context.User.UserId;
var openOrder = session.EntitySet<IBookOrder>(lockOptions)
.Where(bo => bo.User.Id == currUserId && bo.Status == OrderStatus.Open).FirstOrDefault();
if (openOrder == null && create) {
var user = session.GetEntity<IUser>(Context.User.UserId);
openOrder = session.NewOrder(user);
}
return openOrder;
}
public IList<ISecretQuestion> GetAllSecretQuestions(IEntitySession session)
{
var mask = SecretQuestionFlags.Disabled | SecretQuestionFlags.Private;
var query = from q in session.EntitySet<ISecretQuestion>()
where (q.Flags & mask) == 0 //we look for public and enabled
orderby q.Number
select q;
var questions = query.ToList();
return questions;
}
public ILogin GetLogin(IEntitySession session)
{
var context = session.Context;
var user = context.User;
context.ThrowIf(user.Kind != UserKind.AuthenticatedUser, ClientFaultCodes.InvalidValue, "User", "User must be authenticated.");
var query = session.EntitySet<ILogin>();
if(user.UserId == Guid.Empty)
query = query.Where(lg => lg.AltUserId == user.AltUserId);
else
query = query.Where(lg => lg.UserId == user.UserId);
var login = query.FirstOrDefault();
return login;
}