How to check if an entry exists in a separate SQL Server table, and change which function is linked depending on whether it exists or not

Question

Before I start, I'm relatively new to coding and was due to start my first junior role in the upcoming future, so my current skill level is pretty basic.

I'm creating a personal C# ASP.NET MVC web application to track music events that users go to, and the artists that are playing at those events, and which of the artists a user sees. This is all done by the user manually adding events from an Indexed view of all events within the database (SQL Server) which is show in the image linked below.

https://i.stack.imgur.com/HOUGG.png

The controller action:

        public ActionResult GetEvents()
        {
            return View(_trackerService.GetEvents());
        }

The markup for the view:

@model IEnumerable<Tracker.Data.tbl_events>

@{
    ViewBag.Title = "GetEvents";
}

<h2>GetEvents</h2>

<p>
    @Html.ActionLink("Create New", "CreateEvent")
</p>
<table class="table">

    <tr><h2>Upcoming Events</h2>
        <th>
            @Html.DisplayNameFor(model => model.Event_ID)
        </th><th>
            @Html.DisplayNameFor(model => model.Event_Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Event_Date)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Event_Location)
        </th>
    </tr>

    @foreach (var item in Model.Where( x => x.Event_Date >= System.DateTime.Now).OrderBy(x => x.Event_Date))
    {

<tr>
    <td>
        @Html.DisplayFor(modelItem => item.Event_ID)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.Event_Name)
    </td>
    <td>
        @Convert.ToString(string.Format("{0:dd/MM/yyyy}", item.Event_Date)) @*Converts the DateTime data type that ASP.NET uses by default into a string with the format of Date Only (https://stackoverflow.com/a/34990313/12764653)*@
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.Event_Location)
    </td>
    <td>
        @*@Html.ActionLink("Edit", "Edit", new { id = item.Event_ID }) | *@
        @Html.ActionLink("Details", "GetEventDetails", new { Event_ID = item.Event_ID }) |
        @Html.ActionLink("Lineup", "../Event/GetLineup", new { Event_ID = item.Event_ID, Event_Name = item.Event_Name }) |
        @if ((System.Web.HttpContext.Current.User != null) && System.Web.HttpContext.Current.User.Identity.IsAuthenticated)
        {@*Checks to see if there is a current user*@
        @Html.ActionLink("Add to my Events", "../Event/AddToUser", new { User_ID = Convert.ToString(System.Web.HttpContext.Current.User.Identity.GetUserId()).GetHashCode(), Event_ID = item.Event_ID })}
    @*@Html.ActionLink("Delete", "Delete", new { id = item.Event_ID })*@
    </td>
</tr>
    }
</table>

Once a user adds an event to their profile, it creates an entry into the database with the fields (Linked Below):

https://i.stack.imgur.com/YQqHT.png

The controller method for the 'Add To My Events' function:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)] 
public ActionResult AddToUser(tbl_eventhistory _event)
{
        try
        {
            _trackerService.AddToUser(_event);
            return RedirectToAction("GetEvents");
        }
        catch
        {
            return View();
        }
}

I understand that with the way it is currently, this would most likely have to be done either in the controller or View, and it would be done to check 'tbl_eventhistory' to see if an entry exists with the current users User_ID and a specific Event_ID, however I'm unsure of how to actually do this so any help would be greatly appreciated.

EDIT: The table shown is an indexed view of 'tbl_events'. When a user adds an event to their profile from this view, it creates an entry in a different table called 'tbl_eventhistory' using the parameters for that specific event, which is related through a foreign key on the Event_ID (tbl_event's PK). When an event is added to tbl_eventhistory by the user, I want to remove the link 'Add To User' from the view for that specific event only.

Answer 1

You already have a Delete link in your Razor view - that seems to be good enough:

@Html.ActionLink("Delete", "../Event/DeleteEvent", new { id = item.Event_ID })

And then your EventController would look something anlog these lines:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)] 
public ActionResult DeleteEvent(int id)
{
        try
        {
            _trackerService.DeleteForUser(HttpContext.Current.User.Identity.GetUserId(), _event); // this is where you verify whether currently logged in user has access and delete their associated event. 
            //depending on your data access framework of choice you should aim to fire a query like so: 
            //DELETE FROM table WHERE User_Id = :user_id AND Event_Id = :id 
            //that would cover you for users deleing only items belonging to them
            return RedirectToAction("GetEvents");
        }
        catch
        {
            return View();
        }
}

now to point out two security issues with your AddEvent code:

@Html.ActionLink("Add to my Events", "../Event/AddToUser", new { User_ID = Convert.ToString(System.Web.HttpContext.Current.User.Identity.GetUserId()).GetHashCode(), Event_ID = item.Event_ID })}

1. GetHashCode() is not what you think it is: using it as identifier is unsecure because collisions on default implementation are very likely
2. there's no need to even pass the user Id back from client: your controllers have access to HttpContext object so you should be able to just grab that User_ID on server side

and to attempt to answer your questions in title:

How to check if an entry exists in a separate SQL Server table,

I don't believe you need it for purposes of deleting events - see comments in the code above

and change which function is linked depending on whether it exists or not

I an not sure if I get this part. I feel you might be able to answer both of these yourself after having a look through this other SO question on SQL JOIN. Otherwise I (and the community here) will need more details on your actual use case and goal

Answer 2

Managed to finally find a solution. Please forgive me if the terminology is incorrect.

I already had a function to return an indexed view of a specific users events (DAO Class code below)

        public IList<tbl_eventhistory> GetUserEvents(string User_ID)
        {
            IQueryable<tbl_eventhistory> _eventHistory;
            _eventHistory = from tbl_eventhistory in _context.tbl_eventhistory where tbl_eventhistory.User_ID == User_ID select tbl_eventhistory;
            return _eventHistory.ToList<tbl_eventhistory>();
        }

Thinking how to use this data in a view with a different bound Model, I thought about using a ViewBag and came across this: "How to check if a List in a ViewBag contains string". From this answer I modified my 'GetEvents' function to the following:

public ActionResult GetEvents()
        {
            List<string> UsersEvents = new List<string>();
            foreach (var item in _trackerService.GetUserEvents(User_ID))
            {
                UsersEvents.Add(item.Event_ID.ToString());
            }
            ViewBag.MyEvents = _trackerService.GetUserEvents(User_ID);
            ViewBag.MyEvents = UsersEvents;
            return View(_trackerService.GetEvents());
        }

If my understanding is correct, this creates a List which is populated by calling the 'GetUserEvents' action and loops through each entry in the result. Moving this list to a ViewBag then allows me to modify the 'GetEvents' ActionLinks to include an additional IF statement inside the existing statement checking whether a user is logged in:

@if ((System.Web.HttpContext.Current.User != null) && System.Web.HttpContext.Current.User.Identity.IsAuthenticated)

if (((IList<string>)ViewBag.MyEvents).Contains(item.Event_ID.ToString())){ 
       <p>TEST</p> }
else{
       @Html.ActionLink("Add to my Events", "../Event/AddToUser", new { User_ID = Convert.ToString(System.Web.HttpContext.Current.User.Identity.GetUserId()).GetHashCode(), Event_ID = item.Event_ID })}
            }

Which resulted in "Test" being shown instead of the link to 'Add To User' where appropriate (shown below with the final event not being present in 'tbl_eventhistory'):

http://icecream.me/b459c9d17854b081a0804692f67c3aa3

I understand this is probably the furthest from being the most efficient way of doing it, but I'm just glad it now works after a LONG time trying to figure it out.