public List <Note> GetAllNotesForQuestion(string userEmail, int questionId, PagingAttributes pagingAttributes)
{
using var db = new SovaDbContext();
return(db.Notes.Where(n => n.QuestionId == questionId && n.UserEmail == userEmail)
.Skip(pagingAttributes.Page * pagingAttributes.PageSize)
.Take(pagingAttributes.PageSize)
.ToList());
}
public List <Note> GetNotesByUserEmail(string userEmail, PagingAttributes pagingAttributes)
{
using var db = new SovaDbContext();
return(db.Notes.Where(n => n.UserEmail == userEmail)
.OrderBy(n => n.QuestionId)
.Skip(pagingAttributes.Page * pagingAttributes.PageSize)
.Take(pagingAttributes.PageSize)
.ToList());
}
public bool UpdateNote(Note note)
{
using var db = new SovaDbContext();
if (NoteExcist(note.Id))
{
db.Notes.Update(note);
return(db.SaveChanges() > 0);
}
return(false);
}
public bool UpdateUser(AppUser user)
{
using var db = new SovaDbContext();
if (UserExcist(user.Email))
{
db.AppUsers.Update(user);
return(db.SaveChanges() > 0);
}
return(false);
}
public bool DeleteNoteById(int noteId)
{
using var db = new SovaDbContext();
var note = db.Notes.Find(noteId);
if (note == null)
{
return(false);
}
db.Notes.Remove(note);
return(db.SaveChanges() > 0);
}
public bool DeleteUserByEmail(string useremail)
{
using var db = new SovaDbContext();
var email = db.AppUsers.Find(useremail);
if (email == null)
{
return(false);
}
db.AppUsers.Remove(email);
return(db.SaveChanges() > 0);
}
public bool DeleteSearchHistoryById(int historyId)
{
using var db = new SovaDbContext();
var history = db.SearchHistories.Find(historyId);
if (history == null)
{
return(false);
}
db.SearchHistories.Remove(history);
return(db.SaveChanges() > 0);
}
public Note CreateNote(Note note)
{
using var db = new SovaDbContext();
db.Notes.Add(note);
int changes = db.SaveChanges();
if (changes > 0)
{
return(note);
}
else
{
return(null);
}
}
public bool DeleteSearchHistoryByUserEmail(string userEmail)
{
using var db = new SovaDbContext();
var history = db.SearchHistories.Where(u => u.Email == userEmail);
if (history == null)
{
return(false);
}
foreach (var item in history)
{
db.SearchHistories.Remove(item);
}
return(db.SaveChanges() > 0);
}
public SearchHistory CreateSearchHistory(SearchHistory history)
{
using var db = new SovaDbContext();
db.SearchHistories.Add(history);
int changes = db.SaveChanges();
if (changes > 0)
{
return(history);
}
else
{
return(null);
}
}
public List <SearchResult> SearchByScore(string fromScore, string toScore)
{
using var db = new SovaDbContext();
return(db.SearchResults.FromSqlRaw("select * from search_by_score(" + fromScore + "," + toScore + ")").Select(x => new SearchResult
{
QuestionId = x.QuestionId,
AnswerId = x.AnswerId,
Type = x.Type,
Body = x.Body,
Title = x.Title,
Score = x.Score,
Tags = x.Tags,
CreationDate = x.CreationDate
}).ToList());
}
public List <SearchResult> SearchByTag(params string[] tags)
{
using var db = new SovaDbContext();
var s = BuildStringFromParams(tags);
return(db.SearchResults.FromSqlRaw("select * from search_by_tags(" + s + ")").Select(x => new SearchResult
{
QuestionId = x.QuestionId,
AnswerId = x.AnswerId,
Type = x.Type,
Body = x.Body,
Title = x.Title,
Score = x.Score,
Tags = x.Tags,
CreationDate = x.CreationDate
}).ToList());
}
public List <SearchResult> SearchByAcceptedAnswer(bool accepted, params string[] keywords)
{
using var db = new SovaDbContext();
var s = BuildStringFromParams(keywords);
var acc = accepted ? "'yes'" : "'no'";
return(db.SearchResults.FromSqlRaw("select * from search_by_acceptedanswer(" + acc + "," + s + ")").Select(x => new SearchResult
{
QuestionId = x.QuestionId,
AnswerId = x.AnswerId,
Type = x.Type,
Body = x.Body,
Title = x.Title,
Score = x.Score,
Tags = x.Tags,
CreationDate = x.CreationDate
}).ToList());
}
public AppUser CreateUser(string name, string email, string password, string salt)
{
using var db = new SovaDbContext();
var user = new AppUser()
{
Name = name,
Email = email,
Password = password,
Salt = salt
};
db.AppUsers.Add(user);
int changes = db.SaveChanges();
if (changes > 0)
{
return(user);
}
else
{
return(null);
}
}
public int NumberOfNotesPerQuestion(int questionId)
{
using var db = new SovaDbContext();
return(db.Notes.Where(n => n.QuestionId == questionId).Count());
}
public int NumberOfNotesPerUser(string userEmail)
{
using var db = new SovaDbContext();
return(db.Notes.Where(n => n.UserEmail == userEmail).Count());
}
public Note GetNoteById(int noteId)
{
using var db = new SovaDbContext();
return(db.Notes.Find(noteId));
}
public List <SearchHistory> GetSearchHistoryByUserEmail(string userEmail)
{
using var db = new SovaDbContext();
return(db.SearchHistories.Where(n => n.Email == userEmail).ToList());
}
public AppUser GetUserByEmail(string email)
{
using var db = new SovaDbContext();
return(db.AppUsers.Find(email));
}