CODEWITHSUNDEEP
c#linqlinq-to-entities
AlexC
AlexC

Reputation: 10756

LINQ to entities Child queries where Parent has certain properties

Using C# and LINQ to entities I have a problem with child and parent entity searching. In conceptual terms I am trying to get a Collection of IEnumerable children where these children have certain properties and also the parents of these children have certain properties.

In concrete terms I have Routes and Streets which have a many to many relationship. I am trying to find Streets on a specific Route where the Street has a positive property of LeftNote or RightNote (LeftNote and RightNote are strings and I am searching for strings that are not empty space).

I have the following entities (cut down for clarity)

public class Route
{
    public int RouteID { get; set; }

    public virtual ICollection<Street> Streets { get; set; }
}

public class Street
{
    public string LeftNote { get; set; }

    public string RightNote { get; set; }

    public virtual ICollection<Route> Routes { get; set; }
}

I have the following LINQ expression: 

var streets = this.repository.Routes
                        .Where(r => r.RouteID == routeId).FirstOrDefault()
                        .Streets
                        .Where(s => s.LeftNote.Length > 0 || s.RightNote.Length > 0);

This works perfectly until I run this against entity data where the Route exists but there are no streets that have LeftNotes or RightNotes. In these cases I get a NullReference exception. I am trying to find a way to properly express this query which handles the absence of Streets with LeftNotes (there should always be a Route which matches routeId. If not that is a valid exception case and should throw and error).

EDIT: The issue seems to be around null strings rather than anything related to the LINQ construct.

Upvotes: 2

Views: 1626

Answers (6)

tukaef
tukaef

Reputation: 9214

Just use String.IsNullOrEmpty().

var streets = this.repository
                .Routes
                .Where(r => r.RouteID == routeId).FirstOrDefault()
                .Streets
                .Where(s => !String.IsNullOrEmpty(s.LeftNote) ||
                            !String.IsNullOrEmpty(s.RightNote));

Upvotes: 1

Marshal
Marshal

Reputation: 6651

var streets = this.repository
                  .Routes
                  .Where(r => r.RouteID == routeId).FirstOrDefault()
                  .Streets
                  .Where(s => s.LeftNote !=null ? s.LeftNote.Length > 0 : false
                          || s.RightNote !=null ? s.RightNote.Length > 0 : false);

Havn't tested it, but think it should work.

OR

this.repository.Streets(s=>(s.LeftNote.Length >0 || s.RightNote.Length > 0 ) 
                             && s.Routes.routeId==routeId));

Upvotes: 1

Phil
Phil

Reputation: 42991

Do you actually mean

var streets = repository.Streets.Where
       (s => s.Routes.Any(r => r.RouteID == routeId )
             && (s.LeftNote.Length > 0 || s.RightNote.Length > 0));

Upvotes: 1

Eirik
Eirik

Reputation: 4205

Try changing the LINQ expression to this

var streets = this.repository.Routes
                  .Where(r => r.RouteID == routeId).FirstOrDefault()
                  .Where(r => r.Streets.Any(s => s.LeftNote.Length > 0 || s.RightNote.Length > 0));

Upvotes: 0

sarwar026
sarwar026

Reputation: 3821

Please try the following:

var streets = this.repository.Routes
                             .Where(r => r.RouteID == routeId).FirstOrDefault()
                             .Streets
                             .Where(s => s.LeftNote ? s.LeftNote.Length > 0 : false || s.RightNote ? s.RightNote.Length > 0 : true);

Update

var streets = this.repository.Streets
                             .Where(s => s.Routes.RouteID == routeId && s.LeftNote.Length > 0 || s.RightNote.Length > 0);

Update code is after inserting your new code.

The reason to use false first is to force the validation of second or clause. Not sure whether the following works.

Upvotes: 0

NinjaNye
NinjaNye

Reputation: 7126

Does it have to be executed in one query

IEnumerable<Street> streets = Enumerable.Empty<Street>();
var route = this.repository
                .Routes
                .Where(r => r.RouteID == routeId).FirstOrDefault()

if(route != null && route.Streets.Any())
{
    streets = route.Streets
                   .Where(s => s.LeftNote.Length > 0 
                            || s.RightNote.Length > 0);
}

Upvotes: 0

Related Questions