Linq Query and Lambda

[shapper]

Hello,

I am getting a list of regions as follows:

return _context.Regions.Select(r => new RegionModel {
Id = r.Id,
Name = r.Name
}).OrderBy(r => r.Name).AsQueryable();

However I need to get only the Regions which are related with Centers.

So I need to check _context.Centers and get all used Regions Ids from
each Center.Region.Id and then get all the Regions with those Ids ...

Is this possible to do with a lambda expression ...

I think it is possible but I am a little bit confused on this.

Thank You,
Miguel

 

[Peter Duniho]

Hello,

I am getting a list of regions as follows:

return _context.Regions.Select(r => new RegionModel {
Id = r.Id,
Name = r.Name
}).OrderBy(r => r.Name).AsQueryable();

However I need to get only the Regions which are related with Centers.

What's a Region? What's a Center? How do they relate to each other?

So I need to check _context.Centers and get all used Regions Ids from
each Center.Region.Id and then get all the Regions with those Ids ...

Can more that one Region have the same Id? If not, then why not just
get your Region instances from the Center instances? If so, then why is
it valid to include _all_ Region instances with a given Id just because
_a_ Region with that Id was associated with a Center?

Is this possible to do with a lambda expression ...

I think it is possible but I am a little bit confused on this.

Take your time. Work out the relationship carefully, and create a
problem statement that clearly describes exactly the connections you are
trying to establish and/or represent.

There's a good chance that if you simply and clearly describe _exactly_
what you are trying to do, the solution will be immediately apparent to
you. But if not, then at least you have a good way to communicate to
others (like those of us reading your post) what you're trying to do.

If you can't communicate the problem clearly, it shouldn't be surprising
if we can't figure out a good solution.

Pete

 

[shapper]

What's a Region?  What's a Center?  How do they relate to each other?


Can more that one Region have the same Id?  If not, then why not just
get your Region instances from the Center instances?  If so, then why is
it valid to include _all_ Region instances with a given Id just because
_a_ Region with that Id was associated with a Center?



Take your time.  Work out the relationship carefully, and create a
problem statement that clearly describes exactly the connections you are
trying to establish and/or represent.

There's a good chance that if you simply and clearly describe _exactly_
what you are trying to do, the solution will be immediately apparent to
you.  But if not, then at least you have a good way to communicate to
others (like those of us reading your post) what you're trying to do.

If you can't communicate the problem clearly, it shouldn't be surprising
if we can't figure out a good solution.  

Pete

Sorry, you are right. First here it is my SQL tables:

create table dbo.Centers
(
Id int identity not null,
RegionId int not null,
[Name] nvarchar(200) not null,
[Open] bit not null constraint DF_Centers_Open default(0),
constraint PK_Centers primary key clustered(Id)
) -- Centers

create table dbo.Regions
(
Id int identity not null,
[Name] nvarchar(200) not null,
constraint PK_Regions primary key clustered(Id)
) -- Regions

alter table dbo.Centers
add constraint FK_Centers_Regions foreign key(RegionId) references
dbo.Regions(Id) on delete cascade on update cascade;

Then I used Linq2Sql to model these tables.

My objective would be to get a list of all Regions which contains at
least one Opened Center.

And I came up with the following, which is working:

return _context.Regions.SelectMany(r => r.Centers, (r, l) => new
{ r.Id, r.Name, Centers = r.Centers.Where(c => c.Open == true) })
.Where(o => o.Centers.Count() > 0)
.OrderBy(o => o.Name)
.Select(o => new ModelRegion {
Id = o.Id,
Name = o.Name
});

What do you think? Maybe it could be improved ... The "l" is not doing
anything ...

Thank You,
Miguel

 

[Peter Duniho]

[...]
And I came up with the following, which is working:

return _context.Regions.SelectMany(r => r.Centers, (r, l) => new
{ r.Id, r.Name, Centers = r.Centers.Where(c => c.Open == true) })
.Where(o => o.Centers.Count() > 0)
.OrderBy(o => o.Name)
.Select(o => new ModelRegion {
Id = o.Id,
Name = o.Name
});

What do you think? Maybe it could be improved ... The "l" is not doing
anything ...

Yes, I think the fact you're not using "l" does suggest an improper use
of the SelectMany() method. Your final enumeration from that method
is a collection of every Region represented potentially multiple times,
once for each Center that refers to it. That doesn't seem very
efficient to me, especially since you don't do anything later to deal
with the duplicate Region instances.

(By the way, not relevant here because the rest of the query needs
fixing anyway, but the expression "o.Centers.Count() > 0" really should
be "o.Centers.Any()"…counting the entire enumeration is potentially much
more expensive than just seeing if it's non-empty).

Instead, if you're going to approach it from the direction of the Region
objects, what you really want are just Region instances that have any
Center related to it that is open. That would look more like this:

var result = _context.Regions
.Where(r => r.Centers.Any(c => c.Open))
.OrderBy(r => r.Name)
.Select(r => new ModelRegion
{
Id = r.Id,
Name = r.Name
});

Or using the LINQ syntax:

var result = from region in _context.Regions
where region.Centers.Any(c => c.Open)
orderby region.Name
select new ModelRegion { Id = region.Id, Name = region.Name };

I'm not completely sure I understand the data. I'm not a SQL expert, so
forgive me if I missed the point.

My assumption in the above is that any given Center has only one Region.
Thus, looking at each Region, you visit each Center at most once, and
may avoid visiting some Centers, if they are related to a Region that
has at least one other Center that's "open" and which comes before it in
the Region's collection of Center instances.

If that assumption isn't valid, then it may be more efficient to weed
out all the Center instances that aren't "open" first, and then figure
out the unique Region instances associated with those. That might look
something like this:

var result = from region in
(from center in _context.Centers
where center.Open
select new
{
Id = center.Region.Id,
Name = center.Region.Name
})
.Distinct()
orderby region.Name
select new ModelRegion { Id = region.Id, Name = region.Name };

Pete