CODEWITHSUNDEEP
entity-framework
Bronzato
Bronzato

Reputation: 9332

Perform a query on EF but exclude specific elements from the results

Let's say I have to manage gates and the availability of these gates.

Here is my model:

public class Gate
{
    public int GateID { get; set; }
    public string GateName { get; set; }
}

public class GateNA  ---> NA: NotAvailable
{
    public int GateNAID { get; set; }
    public int GateID { get; set; }
    public DateTime Date { get; set; }
}

Now lets say I have the following sample data:

For Gates:

GateID: 1 GateName: "San Antonio Gate"

GateID: 2 GateName: "Santa Fe Gate"

GateID: 3 GateName: "Santa Maria Gate"

These gates are not available at a specific date (>> GateNA class):

GateIDNA: 1 GateID: 2 Date: 2012/06/01

GateIDNA: 2 GateID: 1 Date: 2012/08/08

I'm able to retrieve all the gates beginning with "Santa" like this:

var gateRepository = unitOfWork.Create<Gate>();
var gates = gateRepository.Find(m => m.GateName.Contains("Santa"));

My question is how can I retrieve all the gates beginning with "Santa" AND AVAILABLE between 2012/06/01 and 2012/06/05. So we have to check in GateNA...

The result should be here only one element: the GateID 3.

Any idea?

Thanks.

Upvotes: 0

Views: 305

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

The simplest solution is adding navigation property for GateNA collection to your Gate entity and use it in the query:

gateRepository.Find(m => m.GateName.Contains("Santa") && 
                         !m.GateNAs.Any(g => g.Date == selectedDate));

If you don't add the property you will have to perform manual join in LINQ query.

Upvotes: 1

Taha Paksu
Taha Paksu

Reputation: 15616

why don't you restructure your class like:

public class Gate
{
    public int GateID { get; set; }
    public string GateName { get; set; }
    public List<GateNA> NotAvailableDates { get; set; }
}

public class GateNA  ---> NA: NotAvailable
{
    public int GateNAID { get; set; }
    public int GateID { get; set; }
    public DateTime Date { get; set; }
}

then 

var gates = gateRepository.Find(
    m => m.GateName.Contains("Santa") && 
   (m.NotAvailableDates.Find(
        d => d.Date==new DateTime(2012,06,01)
   )==null
);

Upvotes: 0

Related Questions