C# Linq query returning more than one match

Question

I want to get one boat item from the Boat table only if that boat is not in the RentBoat table and has a value on the IsOnRent column set to true.

This is my code so far:

db.Boats.SingleOrDefault(x => !db.RentBoats.Any(s => s.BoatID == x.BoatID && s.IsOnRent == true));

The error I am getting is:

The sequence contains several elements

Where is my code wrong? Any help would be great.

Essentially I want what this query does but I want it to return the matched item from an Id or return null.(a singel item not a list)

db.Boats.Where(boat => !db.RentBoats.Any(x => x.BoatID == boat.BoatID && x.IsOnRent == true)).ToList();

public class Boat
{
    [Key]
    public int BoatID { get; set; }

    public string BoatName { get; set; }
}

public class RentBoat
{
    [Key]
    [Required]
    public int RentBoatID { get; set; }

    [ForeignKey("Boat")]
    public int BoatID { get; set; }

    public string BoatName { get; set; }
    public bool IsOnRent { get; set; }

    public virtual  Boat Boat { get; set; }
}

EDIT

This is how I solved it in the end:

    using (var db = new BoatContext())
            {
                List<RentBoat> IsBoatRented = db.RentBoats.Where(x => x.BoatID == boatId && x.IsOnRent == true).ToList();
                if (IsBoatRented.Count() == 0)
                {
                    returnedBoat = db.Boats.FirstOrDefault(x => x.BoatID == boatId);
                }                  

            }

I know it isn't as efficient or good looking as one single query. But couldn't make this with one query. If somebody can rewrite these queries to one query. Fell free to help. I would appreciate it.

Doesn't `Boats` have a navigational property to `RentBoats`? — Camilo Terevinto, Mar 18 '18 at 14:28
after lookint at your class structure , this query `!db.RentBoats.Any(s => s.BoatID == x.BoatID && s.IsOnRent == true)` will return you mutiple recrods , it will return you all the boats where `IsOnRent=false`..i think you should double check your table records and improve you query accordingly... — Pranay Rana, Mar 18 '18 at 14:37
as I suggested you ..you need `where` condition with distinct not `SingleOrDegault`..... — Pranay Rana, Mar 18 '18 at 14:39

score 2 · Answer 1 · answered Mar 18 '18 at 14:19

By using SingleOrDefault you're saying that there should ever only be one record matching the condition. The error says there are multiple rows in the database which match what you're asking for.

If you want one result but don't really care if there is only one successful match use FirstOrDefault instead. It will return one row if there is one or more records matching the predicate or null if there is none.

Pranay Rana · Answer 2 · 2018-03-18T14:45:02.813

2

you should try like this , get all non rented boats (your query may give you boat with same id mutiple times) and to avoid that appply group by

var allNotRendedBoats= from b in db.Boats
                          .Where(boat => !db.RentBoats
                          .Any(x => x.BoatID == boat.BoatID && x.IsOnRent == true))
                 group b by b.BoatID into g
select g.First();

if its not going to return you same boat multiple times than just put where conditon instead of SingleOrDefault give you output

seemd like issue with ! condition

db.Boats.SingleOrDefault(x => 
    !db.RentBoats.Any(s => s.BoatID == x.BoatID && s.IsOnRent == true));

it should be

db.Boats.SingleOrDefault(x => 
     db.RentBoats.Any(s => s.BoatID == x.BoatID && s.IsOnRent == true));

if you do ! then you are requested more then one element

So if i go through your code , then seems like you are request all element which are not maching this condition !db.RentBoats.Any(s => s.BoatID == x.BoatID && s.IsOnRent == true))

SingleOrDefault is useful when you are expecting only one element,

if you want to get first element then I suggest make use of FirstOrDefault

edited Mar 18 '18 at 14:45

answered Mar 18 '18 at 14:20

Pranay Rana

175,020
35
237
263

I totally missed that. Good catch! But even with that fix I think it's safe to assume there are more than one boat available for rental and `SingleOrDefault` should really be `FirstOrDefault`. – MarcinJuraszek Mar 18 '18 at 14:22
@MarcinJuraszek - thanks , i updated same thing in my answer ... :) hope it helps him – Pranay Rana Mar 18 '18 at 14:23
In the RentBoat table there will be alot of boats with the same Id. I want to cross match when I get the boats from table Boats that I dont get Boats that are rented out already. – AllramEst Mar 18 '18 at 14:24
@AllramEst -if you want to figure out what is the issue then i suggest you apply where condtion and check how many element is returned by condition ... – Pranay Rana Mar 18 '18 at 14:25
@AllramEst - can you please try and let me know ...add wehere condtion and check the lenght and print element retunred ... – Pranay Rana Mar 18 '18 at 14:27

C# Linq query returning more than one match

2 Answers2