Search query database first mvc

Question

I would like to create a search button which would enable a user to search for a member and load associated details. I am using also Ajax BeginForm.

There are 3 classes/tables:

    public Book()
    {
        this.Loans = new HashSet<Loan>();
    }

    public int ISBN { get; set; }
    public string Name { get; set; }
    public string Author { get; set; }

    public int OnLoan { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Loan> Loans { get; set; }
}
   Loan Class
  public partial class Loan
{
    public int LoanId { get; set; }
    public System.DateTime CheckOutDate { get; set; }
    public System.DateTime ReturnDate { get; set; }
    public decimal FinePrice { get; set; }
     public Nullable<int> MemberId { get; set; }
    public Nullable<int> ISBN { get; set; }

    public virtual Book Book { get; set; }
    public virtual Member Member { get; set; }
}

Member Class:

  public partial class Member
    {
        public Member()
        {
            this.Loans = new HashSet<Loan>();
        }

        public int MemberId { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string PostCode { get; set; }
        public int TelNo { get; set; }


        public virtual ICollection<Loan> Loans { get; set; }
    }

In my BHistory Controller:

public ActionResult HistorySearch(string qh)
{
    var history = Gethistory(qh);

    return PartialView(history);


}
private List<Loan> Gethistory(string searchString)
{
    return db.Loans
        .Where(a => a.MemberId.ToString().Contains(searchString) && a.Book.OnLoan == 1)
       .ToList();
}

So at the moment the search works fine, it returns the correct result, in the browser the user enters memberid and a list of results appear. However I only want the last of the results to appear. Not a list of results

so instead of using ToList(), I want to use LastorDefault(), but I get the error message: cannot convert Models.Loan to collections.generic.list.

So I edited the ActionMethod

private Loan Gethistory(string searchString)
    {
        return db.Loans
            .Where(a => a.MemberId.ToString().Contains(searchString) && a.Book.OnLoan == 1)
           .LastorDefault();

However when I run this I get the error message:

LINQ to Entities does not recognize the method 'Library1.Models.Loan LastOrDefaultLoan' method, and this method cannot be translated into a store expression.

In conclusion how do I get the last of the results to appear, not a list.

This question is different from questions that refer to answers with the use of Last() or LastorDefault() because here even if we switch to using OrderByDescending() and First() or FirstorDefault() the answer/result would not work. The error also lied in the ability of returning a list even when I thought the answer should not return a list

Thanks

Also please include the source code for the partial view `HistorySearch`. — mjwills, Aug 01 '17 at 12:34
You should also add an ORDER BY to ensure you get the row you want. — Crowcoder, Aug 01 '17 at 12:37

score 3 · Accepted Answer · answered Aug 01 '17 at 12:43

3

The problem is that the model on your page is looking for a List<Loan>, and you're only sending it a Loan. As of the time that I posted this answer, you don't have your controller method or view as part of your question, so I would change your query to this:

private List<Loan> Gethistory(string searchString)
{
    var loan = db.Loans
                 .Where(a => a.MemberId
                              .ToString()
                              .Contains(searchString) && 
                             a.Book.OnLoan == 1)
                 .OrderByDescending(a => a.LoanId)
                 .FirstOrDefault();

    return new List<Loan> { loan };
}

As Stefan said in his answer, SQL doesn't have a LastOrDefault method, so you need to write your query in a way that is easier for EF to convert to SQL.

Alternatively, you could change your View to have a model that is a single Loan, though that would make the (presumable) grid you're displaying them/it in a little less intuitive.

answered Aug 01 '17 at 12:43

krillgar

12,596
6
50
86

Thanks I will have a go at this. Appreciate your help – Sam Aug 01 '17 at 13:03
You're welcome. The error message you shared about not being able to convert a `Loan` to `List` is how I was able to deduce this was your issue. The error is being thrown as the Razor engine is trying to generate your page. – krillgar Aug 01 '17 at 13:05
if the user types in an incorrect MemberId then I would want an error message to appear as appose to dbnullexception appearing. How would I implement that? really appreciate this. – Sam Aug 02 '17 at 13:01
If you're doing a single Member or Loan, you don't want to return the same View. You should have multiple views. The view for a single Loan would be more of a details page than a grid showing multiple Loans. – krillgar Aug 02 '17 at 14:25

Stefan · Answer 2 · 2017-08-01T12:51:15.950

Try this:

 return db.Loans
        .Where(a => a.MemberId.ToString().Contains(searchString) && a.Book.OnLoan == 1)
        .OrderByDescending(c => c.MemberId)
        .FirstOrDefault();

SQL does not understand LastOrDefault

So, you'll need to order the list. By using OrderByDescending you will invert this order and, hence, can take the top record.

If you still want to pass in a collection, you can also use the OrderByDescending

return db.Loans
        .Where(a => a.MemberId.ToString().Contains(searchString) && a.Book.OnLoan == 1)
        .OrderByDescending(c => c.MemberId);

See LINQ To Entities does not recognize the method Last. Really? for more details.

Or this similar question:

remove a record with child

Thanks I will check out the links. Appreciate your help – Sam Aug 01 '17 at 13:04 — Sam, Aug 01 '17 at 13:04

Shyam Sa · Answer 3 · 2017-08-01T12:58:59.790

0

Use .LastOrDefault(...) instead of .where

return db.Loans .LastOrDefault(a => a.MemberId.ToString().Contains(searchString) && a.Book.OnLoan == 1);

It also improves your performance.

edited Aug 01 '17 at 12:58

answered Aug 01 '17 at 12:51

Shyam Sa

331
4
8

Search query database first mvc

3 Answers3