Cant search with a different record of a database

Question

I am new here and have been at this for ages and i cant solve the problem. I have the following code in my ShowAllReview Controller:

 public ActionResult Index(string Ordering, string WordFilter, string DisplaySearchResults, int? CounterForPage)
        {
            using (var db = new gamezoneDBEntities())
            {

                ViewBag.Message = TempData["message"];
                ViewBag.CurrentSort = Ordering;
                ViewBag.NameSortParm = String.IsNullOrEmpty(Ordering) ? "GameName" : "";
                ViewBag.DateSortParm = Ordering == "ReleaseYearOfGame" ? "DiscriptionOfGame" : "Date";


                {
                    TempData["DisplaySearchResult"] = DisplaySearchResults;

                    {
                        ViewBag.search = DisplaySearchResults;
                    }
                    if (Request.HttpMethod == "GET")
                    {
                        DisplaySearchResults = WordFilter;
                    }
                    else if (DisplaySearchResults == "")
                    {
                        ViewData["MyMessage"] = "Nothing Has Been Entered.";

                    }

                    else
                    {
                        CounterForPage = 1;
                    }

                    ViewBag.CurrentFilter = DisplaySearchResults;


                    var FullDatabaseItem = from b in db.tblReviews.Include(x => x.tblGame)
                                           select b;



                    if (!String.IsNullOrEmpty(DisplaySearchResults))
                    {

                        FullDatabaseItem = FullDatabaseItem.Include (x => x.tblGame)
                       .Where (b => b.Score.ToUpper().Contains(DisplaySearchResults.ToUpper()));

                    }

                    switch (Ordering)
                    {
                        case "HeadlineName":
                            FullDatabaseItem = FullDatabaseItem.OrderBy(b => b.Score);
                            break;
                        case "DatePosted":
                            FullDatabaseItem = FullDatabaseItem.OrderBy(b => b.Recomendation);
                            break;
                        case "DiscriptionDate":
                            FullDatabaseItem = FullDatabaseItem.OrderBy(b => b.Recomendation);
                            break;
                        default:
                            FullDatabaseItem = FullDatabaseItem.OrderByDescending(b => b.Recomendation);
                            break;
                    }

                    int pageSize = 3;
                    int pageNumber = (CounterForPage ?? 1);
                    var PageNumberResults = FullDatabaseItem.ToPagedList(pageNumber, pageSize);
                    ViewBag.PageNumberResults = FullDatabaseItem.Count();
                    if (PageNumberResults.Any())
                    {

                        return View(PageNumberResults);
                    }

                    return View("ErrorView");
                }
            }
        }

as you can see i have added this code:

var FullDatabaseItem = from b in db.tblReviews.Include(x => x.tblGame)select b;

With out the include on the reviews table the view would throw up the following error:

The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

Now the problem is with this line of code:

FullDatabaseItem = FullDatabaseItem.Include (x => x.tblGame)
                        .Where (b => b.Score.ToUpper().Contains(DisplaySearchResults.ToUpper()));

am trying to get the game name so when the user is using my search he can obtain a record buy just typing in the name of the game. But as you can see the field being searched is "Score" which is the tblReview field as the field i need "GameName" will not work in there as it says it does not exsit.

I need help in solving the problem

Any more information you require please ask me and i shall provide

Thank You

Answer 1

Your entity model has a navigation property from tblReview to tblGame, correct? So you have an entity that looks something like this?

public class tblReview
{
    public virtual tblGame tblGame { get; set; }
    // other properties
}

Is this relationship bi-directional or uni-directional? Meaning, does your tblGame class have a tblReviews collection property like this?

public class tblGame
{
    public virtual ICollection<tblReview> tblReviews { get; set; }
    // other properties
}

If so, then you have a bi-directional association, and you can search fields in one entity from fields in the other entity.

Say for example you want to display a list of reviews for a game with a certain name. You can do that like so:

string gameName = "Pac-Man";
using (var db = new gamezoneDBEntities())
{
    // get reviews for game named pac-man
    var reviews = db.tblReviews.Include(r => r.tblGame)
        .Where(r => r.tblGame.GameName.Equals(gameName, 
            StringComparison.OrdinalIgnoreCase));

    // get game with reviews scored greater than 4
    var games = db.tblGames.Include(g => g.tblReviews)
        .Where(g => g.tblReviews.Any(r => r.Score > 4));
}

So ultimately, I think this is what you are after:

var FullDatabaseItem = db.tblReviews.Include(g => g.tblGame);
if (!string.IsNullOrEmpty(DisplaySearchResults))
{
    FullDatabaseItem = FullDatabaseItem
        .Where(review => review.tblGame.GameName.Contains(DisplaySearchResults));
}

I don't think you need to convert the strings to upper in order to do a case-insensitive match when using .Contains(). Searches for "pAC-mAN" should still return games named "Pac-Man".