//return count of numbers
public int GetUserCount()
{
using (var db = new StackoverflowContext())
{
return(db.User.Count());
}
}
// get all users owning the post-id
public List <Post> GetPostsUser(int id)
{
using (var db = new StackoverflowContext())
{
return(db.Post.Where(p => p.OwnerUserId == id).ToList());
}
}
//return a full user, including all posts.
public User GetUser(int id)
{
using (var db = new StackoverflowContext())
{
return(db.User.Include(c => c.Posts).FirstOrDefault(p => p.Userid == id));
}
}
public History GetHistoryItem(int userId, int linkPostId)
{
using (var db = new StackoverflowContext())
{
return(db.History.FirstOrDefault(h => h.Userid == userId && h.LinkPostId == linkPostId));
}
}
public void DeleteMarking(int uid, int pid)
{
using (var db = new StackoverflowContext())
{
db.Database.ExecuteSqlCommand("delete from marking where user_id = {0} and post_id = {1}", uid, pid);
}
}
public List <History> GetHistory()
{
using (var db = new StackoverflowContext())
{
return(db.History.ToList());
}
}
public int AddMarking(int uid, int pid, string mark)
{
using (var db = new StackoverflowContext())
{
return(db.Database.ExecuteSqlCommand("call add_marking({0},{1},{2})", uid, pid, mark));
}
}
public int AddQuestion(int uid, string body, string title)
{
using (var db = new StackoverflowContext())
{
return(db.Database.ExecuteSqlCommand("call add_question({0},{1},{2})", uid, body, title));
}
}
public List <Marking> UserBookmarks(int id)
{
using (var db = new StackoverflowContext())
{
return(db.Marking.Where(m => m.Postid == id).ToList());
}
}
//private readonly StackoverflowContext _db;
//public DataService() => _db = new StackoverflowContext();
// ------------------------ USERS ------------------------
//public List<User> GetUsers() => _db.User.ToList();
public List <User> GetUsers(int page, int pageSize)
{
using (var db = new StackoverflowContext())
{
return(db.User.OrderBy(x => x.Userid).Skip(page * pageSize).Take(pageSize).ToList());
}
}
// ------------------------ HISTORY & BOOKMARKS ------------------------
public List <History> UserHistory(int id)
{
using (var db = new StackoverflowContext())
{
return(db.History.Where(h => h.Userid == id).ToList());
}
}
public List <LinkedPosts> LinkingToThisPost(int id)
{
using (var db = new StackoverflowContext())
{
return(db.LinkedPosts.Where(lp => lp.PostId == id).ToList());
}
}
public int NumberOfQuestions()
{
using (var db = new StackoverflowContext())
{
return(db.Post.Count(x => x.TypeId == 1));
}
}
public List <Question> SearchQuestionsByTag(string tag, int limit)
{
using (var db = new StackoverflowContext())
{
return(db.Question.FromSql("call search_questions_by_tag({0},{1})", tag, limit).ToList());
}
}
//return a Post, including tags related to the post.
public Post GetPosts_Tags(int id)
{
using (var db = new StackoverflowContext())
{
return(db.Post.Include(c => c.PostTags).FirstOrDefault(p => p.PostId == id));
}
}
public void AddQuestionToHistory(int userId, int postId)
{
using (var db = new StackoverflowContext())
{
db.History.Add(new History(userId, postId));
db.SaveChanges();
}
}
public void RemoveQuestionFromHistory(int userId, int linkPostId)
{
using (var db = new StackoverflowContext())
{
db.History.Remove(GetHistoryItem(userId, linkPostId));
db.SaveChanges();
}
}
//return a full post, including all comments and tags.
public Post GetPost(int id)
{
using (var db = new StackoverflowContext())
{
return(db.Post.Include(p1 => p1.Comments).Include(p1 => p1.PostTags)
.FirstOrDefault(p => p.PostId == id));
}
}
//return a full question
public Question GetQuestion(int id)
{
using (var db = new StackoverflowContext())
{
var question = db.Question.FirstOrDefault(q => q.PostId1 == id);
question?.SetPost(GetPost(id));
return(question);
}
}
public List <Post> BestMatch(string text)
{
using (var db = new StackoverflowContext())
{
return
(db.Post.FromSql("call bestmatch({0})")
.ToList()); // the words in the text needs to be coma seperated and the string needs to be max 5000 in length for this to work
}
}
//return a full answer
public Answers GetAnswer(int id)
{
using (var db = new StackoverflowContext())
{
var answer = db.Answer.FirstOrDefault(q => q.PostId == id);
answer?.SetPost(GetPost(id));
return(answer);
}
}
// ------------------------ POSTS (QUESTIONS / ANSWERS) ------------------------
public List <Post> GetPosts(int page, int pageSize)
{
using (var db = new StackoverflowContext())
{
return(db.Post
.Skip(page * pageSize)
.Take(pageSize)
.ToList());
}
}
public List <Post> GetNewestQuestions(int page, int pageSize)
{
using (var db = new StackoverflowContext())
{
var posts = db.Post.Where(p => p.TypeId == 1 /*&& p.CreationDate>DateTime.Now.AddDays(-1027)*/).OrderByDescending(q => q.CreationDate)
.Skip(page * pageSize).Take(pageSize).ToList();
//var posts = db.NewestQuestions.Skip(page * pageSize).Take(pageSize).ToList();
return(posts);//.Select(post => GetQuestion(post.PostId)).ToList();
}
}
//----- For the wordcloud -----//
public List <WordIndex> GetTfOfWordsInAPost(int id)
{
using (var db = new StackoverflowContext())
{
return(db.WordIndex.Where(i => i.Id == id)
.GroupBy(test => test.Word)
.Select(grp => grp.First())
.ToList());
}
}
public List <Answers> GetAnswers(int id)
{
using (var db = new StackoverflowContext())
{
var answers = db.Answer.Where(x => x.Parentid == id).ToList();
foreach (var answer in answers)
{
answer.SetPost(db.Post.FirstOrDefault(p => p.PostId == answer.PostId));
}
return(answers);
}
}
public List <RankedWords> RankedWordsSearch(string terms /*, int page, int pageSize*/)
{
using (var db = new StackoverflowContext())
{
if (string.IsNullOrEmpty(terms))
{
terms = "Python Dictionary"; //only for testing, parameter shpuld be used
}
terms = terms.Replace(" ", ", ");
var posts = db.RankedWords.FromSql("call ranked_words({0})", terms).OrderByDescending(q => q.Rank).ToList();
//.Skip(page * pageSize).Take(pageSize).ToList();
/*foreach (var p in posts)
* {
* p.Question = GetQuestion(p.Id);
* }*/
return(posts);//.Select(post => GetQuestion(post.Id)).ToList();
}
}
