During this session, we explain Join Entities in EF Core and talk about Search in EF Core and work with lambda expressions (LINQ) for this purpose. Also, we implement these features on the ASP.NET Core Web App that we developed until now. Based on the added entities to the Web App that we developed for this tutorial, Now we can create a proper join view with them to deliver a better user experience to the end-user. Also, we will add the Search feature to the Web App and show you the way that you can use LINQ.
Join Entities in EF Core
For Joining entities, we can use the Join method and then connect the related properties of the entities. As you can see in the below figure, Category ID is displayed in the list instead of Category Name which is not a proper option in terms of user experience.
To solve the mentioned issue, we need to Join the Cost entity which just has CategoryID, and the Category entity which has both CategortID and CategoryName. Then, we can select the properties that we need for the View.
To implement this feature on the Web App, first, we need a ViewModel with the selected properties.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
public class CostList { public int ID { get; set; } public decimal Amount { get; set; } public string Comment { get; set; } public string CategoryName { get; set; } public PaymentMethods? PaymentMethod { set; get; } } |
Then, we need to add a new Method to the ICostRepository interface.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
public interface ICostRepository { Cost GetCostByID(int id); IEnumerable<Cost> GetAllCost(); Cost Create(Cost NewCost); Cost Update(Cost UpdateCost); Cost Delete(int id); List<CostList> GetCostList(string searchby, string searchfor); } |
Next, we implement the declared method in the respective repository or repositories.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
public List<CostList> GetCostList() { var CL = context.Costs.Join(context.Categories, costen => costen.CategoryID, caten => caten.ID, (costen, caten) => new { costen, caten }). Select(sel => new { sel.costen.ID, sel.costen.Amount, sel.costen.Comment, sel.costen.PaymentMethod, sel.caten.CategoryName }).ToList(); List<CostList> costList = new(); foreach (var cost in CL) { costList.Add(new CostList { ID = cost.ID, Amount = cost.Amount, Comment = cost.Comment, PaymentMethod = cost.PaymentMethod, CategoryName = cost.CategoryName }); } return costList; } |
As shown above, the entities have been joined with the Join method. Moreover, we use lambda expressions to connect the respective properties. Now, we can use this method to have a list of Costs that includes CategoryName. Thus we use it in CostController/Index Action Method.
|
1 2 3 4 5 6 7 8 9 10 |
[HttpGet] public IActionResult Index() { var costs = costRepository.GetCostList(); return View(costs); } |
Also, we need to modify the Cost/Index View as well.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
@model IEnumerable<CostList> @{ ViewBag.Title = "Cost List"; } <div class="row"> <div class=col-3> <a asp-controller="cost" asp-action="create" class="btn btn-primary mx-2 my-2">Create New Cost</a> </div> </div> <table class="table table-dark"> <thead> <tr> <th>ID</th> <th>Amount</th> <th>Category</th> <th>Comment</th> <th>Payment Method</th> <th colspan="3" class="text-center">Actions</th> </tr> </thead> <tbody> @foreach (var cost in Model) { <tr> <td>@cost.ID</td> <td>@cost.Amount</td> <td>@cost.CategoryName</td> <td>@cost.Comment</td> <td>@cost.PaymentMethod</td> <form asp-controller="cost" asp-action="delete" asp-route-id="@cost.ID" method="post"> <td><a class="btn btn-primary d-block" asp-controller="cost" asp-action="detail" asp-route-id="@cost.ID">View</a></td> <td><a class="btn btn-info btn-block d-block" asp-controller="cost" asp-action="update" asp-route-id="@cost.ID">Edit</a></td> <td><button class="btn btn-danger btn-block d-block" type="submit">Delete</button></td> </form> </tr> } </tbody> </table> |
Hence, the result is the below figure that shows Category Name instead of Category ID.
Search in EF Core
We can Search the list of matched records with the applied conditions in EF Core. For this purpose, we can use the Where method and the comparative operations same as ==, <, >, <=, >=, != and StratsWith, EndsWith, Equal, Contains methods. In this session, we try the == and Contains method and you can try the rest. Worth mentioning that, we can use Contains method when we are looking for records that contain the search phrase.
To implement this feature, we need to add a Search Form to our Cost/Index View.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<form asp-controller="Cost" asp-action="Index" method="get" class="my-2 mx-2"> <div class="input-group"> <select class="form-select" id="inputSearch" name="searchby"> <option selected value="">Search by...</option> <option value="comment">Comment</option> <option value="category">Category</option> </select> <input name="searchfor" class="form-control" id="inputSearch" aria-describedby="inputSearchComment" aria-label="Search"> <button class="btn btn-outline-secondary" type="submit" id="inputSearch">Search</button> <a class="btn btn-outline-primary" type="button" asp-controller="cost" asp-action="index">Clear Search</a> </div> </form> |
The added form method is GET and the form address CostController and Index Action Method. Thus, the search phrases are passed to the Controller via Query String Parameters. So, we need to modify the Controller
|
1 2 3 4 5 6 7 8 9 10 11 |
[HttpGet] public IActionResult Index(string searchby, string searchfor) { //var costs = costRepository.GetAllCost(); var costs = costRepository.GetCostList(searchby, searchfor); return View(costs); } |
Moreover, the Interface and the Repository should be changed to meet the application requirement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
public interface ICostRepository { Cost GetCostByID(int id); IEnumerable<Cost> GetAllCost(); Cost Create(Cost NewCost); Cost Update(Cost UpdateCost); Cost Delete(int id); List<CostList> GetCostList(string searchby, string searchfor); } |
we should apply the Interface changes to the respective repository.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
public List<CostList> GetCostList(string searchby, string searchfor) { var CL = context.Costs.Join(context.Categories, costen => costen.CategoryID, caten => caten.ID, (costen, caten) => new { costen, caten }). Select(sel => new { sel.costen.ID, sel.costen.Amount, sel.costen.Comment, sel.costen.PaymentMethod, sel.caten.CategoryName }).ToList(); if(searchby == "comment" && searchfor != null) { CL = CL.Where(ser => ser.Comment.ToLower().Contains(searchfor.ToLower())).ToList(); } if (searchby == "category" && searchfor != null) { CL = CL.Where(ser => ser.CategoryName.ToLower() == searchfor.ToLower()).ToList(); } List<CostList> costList = new(); foreach (var cost in CL) { costList.Add(new CostList { ID = cost.ID, Amount = cost.Amount, Comment = cost.Comment, PaymentMethod = cost.PaymentMethod, CategoryName = cost.CategoryName }); } return costList; } |