# Querying a database to find notifications to comments

I have a solution what I think is not the best, and I want to make this not so expensive.

I'm using MVC 5 + SignalR (hubs) + EF + UnitOfWork + Generic Repos.

I have a table Comments:

• Id (Guid Pk)
• Comment (Text)
• ParentId (nullable FK to PK (ID) self referencing -> a response to another comment)
• BrowserId (Id gathered from Context.AnonymousId to recognize anon. users)
• IsNotified (if this is a response ParentId not null - will indicate if the response has been notified to original comment)

This is my flow:

1. User opens the landing page
2. After page finish loading SignalR will execute code to find if that user had any response to any of his comments while not connected

This is my SignalR logic, where I think we can make things better:

public void GetUserNews()
{
var news = new List<CommentVm>();

//1) I browse ALL the DB where the comments have my Anonymous this means that are comments that I made.
List<Guid> misComentarios = UnitOfWork.CommentService
.SearchBy(c => c.BrowserId == new Guid(HttpContext.Current.Request.AnonymousID))
.Select(c => c.Id)
.ToList();

//2) Then I go and search all other comments that ARE NOT notified and is a response to users comments because the parent is the users comment.
.SearchBy(c => !c.IsNotified && misComentarios.Contains((Guid)c.ParentId))
.ToList();

//3) If I have a result...
{
//I use automapper to convert to vm.

//then I change the notified property to TRUE
resultados.ForEach(m => m.IsNotified = true);
UnitOfWork.Save();
Clients.Caller.receiveNews(news);  //send the news to client
}
}


My questions and observations are:

1. I search the whole comment base to get all my comments (my AnonymousID)
2. I get all the comments that are not notified whose parentId are in my resulset

This will execute just on landing page, then I will use signalR if a user gets a response while connected.

Do you think there is a much easier way to query the DB and get what I'm looking for?

• For starters, why not use c => c.BrowserId == new Guid(HttpContext.Current.Request.AnonymousID) && !c.IsNotified in one query. If you need more help you should also show what UnitOfWork.CommentService and SearchBy() look like. – Gert Arnold Jan 1 '18 at 15:51