public User GetUser(string username)
{
using var db = new SOContext();
var user = db.Users.FirstOrDefault(x => x.Username == username);
return(user);
}
// public List<Post> GetAuthPosts(int userId)
// {
// var db = new SOContext();
// Console.WriteLine("@@@@@@@@@@@@@ USER ID @@@@@@@@@@@@@@@@");
// Console.WriteLine(@"User id = {0} ", userId);
// if (db.Users.FirstOrDefault(x => x.Id == userId) == null)
// throw new ArgumentException("user not found");
// return _posts;
// }
public List <SearchResult> Search(string[] words, int userId)
{
using var db = new SOContext();
// small hack due to FromSqlRaw parser issue
string str = "";
foreach (var item in words)
{
str += $",'{item}'";
}
try
{
var result = db.SearchResults
.FromSqlRaw($"select * from best_match_with_weight({userId} {str})")
.ToList();
return(result);
}
catch (Exception e)
{
// return null;
throw e;
}
}
public List <Annotation> GetAnnotationsByMarking(int markingId)
{
using var db = new SOContext();
var annotations = db.Annotations.Where(a => a.MarkingId == markingId).ToList();
return(annotations);
}
public Annotation GetAnnotation(int annotationId)
{
using var db = new SOContext();
var annotation = db.Annotations.Find(annotationId);
return(annotation);
}
public List <Marking> GetMarkings(int userid, PagingAttributes pagingAttributes)
{
using var db = new SOContext();
var markings = db.Markings
.Where(m => m.UserId == userid)
.Include(m => m.Annotation)
.Skip(pagingAttributes.Page * pagingAttributes.PageSize)
.Take(pagingAttributes.PageSize)
.ToList();
return(markings);
}
public List <Question> GetQuestions(PagingAttributes pagingAttributes)
{
using var db = new SOContext();
var questions = db.Questions
.Include(q => q.Post)
.Skip(pagingAttributes.Page * pagingAttributes.PageSize)
.Take(pagingAttributes.PageSize)
.ToList();
return(questions);
}
public bool DeleteAnnotation(Annotation annotation)
{
using var db = new SOContext();
try
{
db.Annotations.Remove(annotation);
db.SaveChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public bool DeleteMarking(Marking marking)
{
using var db = new SOContext();
try
{
db.Markings.Remove(marking);
db.SaveChanges();
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
public Owner GetOwner(int ownerId)
{
using var db = new SOContext();
try
{
var result = db.Owners.Find(ownerId);
return(result);
}
catch (Exception e)
{
// return null;
throw e;
}
}
public Tag GetQuestionTags(int questionId)
{
using var db = new SOContext();
try
{
var tags = db.Tags
.Where(t => t.QuestionId == questionId)
.FirstOrDefault();
return(tags);
}
catch (Exception)
{
return(null);
}
}
public List <SearchHistory> GetSearchHistory(int userId)
{
using var db = new SOContext();
try
{
var result = db.SearchHistories
.Where(h => h.UserId == userId)
.ToList();
return(result);
}
catch (Exception e)
{
return(null);
throw e;
}
}
public Comment GetComments(int postId)
{
using var db = new SOContext();
var comments = (from a in db.Comments
join p in db.Posts
on a.PostId equals p.PostId
where a.PostId == postId
select new Comment
{
CommentText = a.CommentText,
CommentScore = a.CommentScore,
UserId = a.UserId,
PostId = a.PostId
}).FirstOrDefault();
return(comments);
}
public List <Annotation> GetAnnotationsByUser(int userId)
{
using var db = new SOContext();
var annotations = (from a in db.Annotations
join m in db.Markings on a.MarkingId equals m.MarkingId
where m.UserId == userId
select new Annotation
{
AnnotationId = a.AnnotationId,
MarkingId = a.MarkingId,
Body = a.Body
}).ToList();
Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@@");
Console.WriteLine(annotations.Count);
return(annotations);
}
public AnswerDbDto GetAnswer(int answerId)
{
using var db = new SOContext();
var answers = (from a in db.Answers
join p in db.Posts
on a.AnswerId equals p.PostId
where a.AnswerId == answerId
select new AnswerDbDto
{
AnswerId = p.PostId,
ParentId = a.PostId,
CreationDate = p.CreationDate,
Score = p.Score,
Body = p.Body
}).FirstOrDefault();
return(answers);
}
public QuestionDbDto GetQuestion(int id)
{
using var db = new SOContext();
var questionDto = (from q in db.Questions
join p in db.Posts
on q.QuestionId equals p.PostId
where q.QuestionId == id
select new QuestionDbDto
{
Title = q.Title,
QuestionId = p.PostId,
CreationDate = p.CreationDate,
ClosedDate = q.ClosedDate,
Score = p.Score,
Body = p.Body
}).FirstOrDefault();
return(questionDto);
}
public bool UpdateAnnotation(Annotation annotation)
{
using var db = new SOContext();
try
{
var oldAnnotation = db.Annotations.Find(annotation.AnnotationId);
oldAnnotation.Body = annotation.Body;
db.Annotations.Update(oldAnnotation);
db.SaveChanges();
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
public List <AnswerDbDto> GetAnswersForQuestion(int questionId)
{
using var db = new SOContext();
var answers = (from a in db.Answers
join p in db.Posts
on a.AnswerId equals p.PostId
where a.PostId == questionId
select new AnswerDbDto
{
AnswerId = p.PostId,
ParentId = a.PostId,
CreationDate = p.CreationDate,
Score = p.Score,
Body = p.Body
}).ToList();
//var comments = (from a in db.Comments
// join p in db.Answers
// on a.PostId equals p.PostId
// where a.PostId == questionId
// select new Comment
// {
// CommentText = a.CommentText,
// CommentScore = a.CommentScore,
// UserId = a.UserId,
// PostId = a.PostId
// }).FirstOrDefault();
//var finalResult = new FinalQuestionAnswerCommentDto()
// {
// AnswerDb = answers,
// Comments = comments
// };
//Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@@@@ FInal result @@@@@@@@");
// Console.Write(answers);
// Console.Write(comments);
//Console.WriteLine(finalResult);
return(answers);
}
public object GetQuestionWithAnswers(int questionId)
{
using var db = new SOContext();
var question = db.Questions
.Where(q => q.QuestionId == questionId)
.Include(q => q.Post).FirstOrDefault();
var answers = (from a in db.Answers
join p in db.Posts
on a.AnswerId equals p.PostId
where a.PostId == questionId
select new
{
Id = p.PostId,
p.CreationDate,
p.Score,
p.Body
}).ToList();
return(new { Question = question, Answers = answers });
}
public int AddAnnotation(Annotation annotation)
{
using var db = new SOContext();
// you can add parameters to the query, as shown here, by list them after the
// statement, and reference them with {0} {1} ... {n}, where 0 is the first argument,
// 1 is the second etc.
try
{
return(db.AnnotationFunction
.FromSqlRaw("select addAnnotation({0}, {1}) as Id", annotation.MarkingId, annotation.Body)
.FirstOrDefault()
.Id);
}
catch (Exception e)
{
return(0);
throw e;
}
}
public User CreateUser(string username, string password, string salt)
{
using var db = new SOContext();
var user = new User()
{
Username = username,
Password = password,
Salt = salt
};
try
{
db.Users.Add(user);
db.SaveChanges();
return(user);
}
catch (Exception e)
{
return(user);
throw e;
}
}
// due to efcore 3.0 still not supporting multiple dataset return we will query the db multiple times for due to efcore's simplicity
public FullPost GetFullPost(int questionId)
{
try
{
using var db = new SOContext();
var questionDbDto = (from q in db.Questions
join p in db.Posts
on q.QuestionId equals p.PostId
join o in db.Owners
on p.OwnerId equals o.UserId
where q.QuestionId == questionId
select new QuestionDbDto
{
QuestionId = q.QuestionId,
Title = q.Title,
CreationDate = p.CreationDate,
ClosedDate = q.ClosedDate,
Score = p.Score,
Body = p.Body,
Owner = o
}).FirstOrDefault();
// Console.WriteLine(JsonConvert.SerializeObject(questionDbDto));
var questionDbDtoComments = db.Comments
.Where(c => c.PostId == questionId)
.ToList();
// assign comments to question
questionDbDto.Comments = questionDbDtoComments;
var answerDbDtos = (from a in db.Answers
join p in db.Posts
on a.AnswerId equals p.PostId
join o in db.Owners
on p.OwnerId equals o.UserId
where a.PostId == questionId
select new AnswerDbDto
{
AnswerId = a.AnswerId,
CreationDate = p.CreationDate,
Score = p.Score,
Body = p.Body,
Owner = o
}).ToList();
var answerIds = new List <int>();
foreach (var item in answerDbDtos)
{
answerIds.Add(item.AnswerId);
}
var answerDbDtoComments = db.Comments
.Where(c => answerIds.Contains(c.PostId))
.ToList();
foreach (var answer in answerDbDtos)
{
answer.Comments = answerDbDtoComments.Where(c => c.PostId == answer.AnswerId).ToList();
}
var marking = db.Markings
.Where(m => m.PostId == questionId)
.FirstOrDefault();
Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@@");
Console.WriteLine(marking);
var tags = GetQuestionTags(questionId);
// map questions and answers + comments to post
FullPost post = new FullPost();
post.Question = questionDbDto;
post.Question.Marking = marking;
post.Answers = answerDbDtos;
post.Tags = tags;
return(post);
}
catch (Exception e)
{
// return null;
throw e;
}
}
public int NumberOfMarkings()
{
using var db = new SOContext();
return(db.Markings.Count());
}
public int NumberOfQuestions()
{
using var db = new SOContext();
return(db.Questions.Count());
}