CODEWITHSUNDEEP
c#asp.net-mvclinqasp.net-mvc-4asp.net-ajax
Sam
Sam

Reputation: 149

Search query database first mvc

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

Upvotes: 0

Views: 512

Answers (3)

Shyam Sa
Shyam Sa

Reputation: 331

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

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

It also improves your performance.

Upvotes: 0

Stefan
Stefan

Reputation: 17698

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

Upvotes: 1

krillgar
krillgar

Reputation: 12815

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.

Upvotes: 3

Related Questions