Linq Query and Lambda

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
0
shapper
2/10/2010 7:07:04 PM
dotnet.languages.csharp 1931 articles. 0 followers. Follow

3 Replies
891 Views

Similar Articles

[PageSpeed] 27

shapper wrote:
> 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
0
Peter
2/10/2010 7:24:50 PM
On Feb 10, 7:24=A0pm, Peter Duniho <no.peted.s...@no.nwlink.spam.com>
wrote:
> shapper wrote:
> > Hello,
>
> > I am getting a list of regions as follows:
>
> > =A0 =A0 =A0 return _context.Regions.Select(r =3D> new RegionModel {
> > =A0 =A0 =A0 =A0 Id =3D r.Id,
> > =A0 =A0 =A0 =A0 Name =3D r.Name
> > =A0 =A0 =A0 }).OrderBy(r =3D> r.Name).AsQueryable();
>
> > However I need to get only the Regions which are related with Centers.
>
> What's a Region? =A0What's a Center? =A0How 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? =A0If not, then why not just
> get your Region instances from the Center instances? =A0If so, then why i=
s
> 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. =A0Work 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. =A0But 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. =A0:)
>
> 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 =3D> r.Centers, (r, l) =3D> new
{ r.Id, r.Name, Centers =3D r.Centers.Where(c =3D> c.Open =3D=3D true) })
        .Where(o =3D> o.Centers.Count() > 0)
        .OrderBy(o =3D> o.Name)
        .Select(o =3D> new ModelRegion {
          Id =3D o.Id,
          Name =3D o.Name
        });

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

Thank You,
Miguel
0
shapper
2/10/2010 7:52:00 PM
shapper wrote:
> [...]
> 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
0
Peter
2/10/2010 9:16:52 PM
Reply:

Similar Artilces:

Querying Dates
Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the st...

Query question.
I need to know how to format a query to produce columns of "best fit" width. Specifically in a Xtab query. Please help, Frank What do you mean by 'best fit width'? The width of a column is really a display issue on whatever form or report will show the results. If you are saying you need to know the maximum length of the data in any column of the entire table, you'd need to take a 2-pass approach where the first pass calculates the max length of each column and the second pass formats the data. -- Dorian "Give someone a fish and they eat fo...

Help creating updateable query
Trying to create an updateable query, but, I keep getting "Operation must use updateable query" message when trying to do the following: Table A: Table B: LOC VALUE LOC VALUE A 10 A <blank> B 40 B <blank> B 20 C <blank> ...

Microsoft query does not recognize tables in Excel spreadsheet
Using Excel 2007 12.0 SP 2 on Windows XP I have created an Excel spreadsheet, C:\Temp\PracDbase.xlsx with two tables: Table1: X Y 1 1 2 2 Table2: X Y 1 1 2 2 Both of these are on the same worksheet, sheet1. The first table occupies the range A1:B3, the second occupies the range D1:E3. Both of these ranges have been converted to tables via Home/Format As Table. If I look up names under Formulas/Name Manager, both of these table names are there and they refer to the correct range. ie, I am certain that the tables Table1 and Table2 exist in C:\Temp\PracDbase.xlsx and...

Help with macro looping and color query function
I have been struggling to get a macro together to perform some tricky coloring functions. With the kind help of a number of people in the 'Excel Worksheet Forum' I have got close to achieving something which works but need help to get the macro to run. Here is where I have got to: The following is an example macro, kindly provided to me by Paul who has been helping me in the worksheet forum: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 19/05/2005 ' ' mycell = Range("A1").Value mycolor = Range("C1").Value Sheets("Sheet2...

Save DB query results as XML file
I am trying to query a SQL Server database, retrieve the results as XML, and save them to a file. I was trying to use the SqlXmlCommand, SqlXmlCommandType.XPath and an xsd to query the database, and load the results into a XmlTextReader. This works fine. But then I get stuck. I don't want to DO anything with the data besides putting it into a file. Should I be loading the results into a stream, instead? Then load the stream into a string, to an XmlDocument, then .Save(...)? Or might a XmlTextWriter have a role in here somewhere? Can anyone give me a hint on how I should appr...

iif, len, replace queries
Hi, I am somewhat of a beginner in regards to my Access skillset. I am trying to create two separate queries and am in need of assistance. I have been trying to use iif, len and replace but am struggling. 1. Text Field (numeric and alphanumeric): If the length of the string is not equal to 10 characters I need to set that value to "9999999999". 2. Text Field (numeric and alphanumeric): I need to remove all "-" and all spaces. From this result I need to display the first 10 characters. If there are less then 10 characters then no value is to be displayed. Thank you...

Multi Query report
Hi All, I have a report that is produced from a query, it is running fine but i would like to add an additional text box on the report to pull the result from a different query column, is this possible? Thanks If both queries have something to key on. Like maybe an ID number or a part number, something that matches. Then use a subreport and link them using link master and child fields. -- Milton Purdy ACCESS State of Arkansas "blake7" wrote: > Hi All, I have a report that is produced from a query, it is running fine but > i would like to add an addi...

update query
How can i build a query where all the rows having -. for example -1, or -2 etc, to be set to o ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1 On Fri, 29 Feb 2008 06:43:35 GMT, "peljo via AccessMonster.com" <u19312@uwe> wrote: >How can i build a query where all the rows having -. for example -1, or -2 >etc, to be set to o ? Create a Query based on your table. On the Criteria line under the relevant field, put your criterion - for instance just -1 or IN (-1, -2) if you want all records with either of ...

Monthly Query
I'm not sure how to do this but I know it is possible. If I have a table that gets created every month with table name NOV 07 and the following month table DEC 07 and so on.........is there a way to have a query to extract the data from NOV 07 table and when its the next month run the same query but for the DEC 07 table. The queries will then be used to create monthly reports. How can this be done? First point. You design is incorrect. There should not be a situation where each month is in a different table. Use a field in your table to identify to which month it belongs. Yo...

Inability to edit or view queries
I have a spreadsheet on a network, that queries an Acess database, on the same network. When I try to view/edit the query the option is greyed out. Why can't I get into the query? I've got read and write priviledges. Any help/ideas would be appreciated. ...

Multiple Tables one Query
Ok, I've read several articles, help files, yadda yadda but I can't seem to make this work. What is the proper way to get information from two tables into one Query or form? help me to set up an email address "Matt" <Matt@discussions.microsoft.com> wrote in message news:08BF7812-9F66-441B-84F0-F30EEC0F8C6D@microsoft.com... > Ok, I've read several articles, help files, yadda yadda but I can't seem > to > make this work. What is the proper way to get information from two tables > into one Query or form? On Fri, 29 Feb 2008 12:34:02 -0800, Mat...

MDE File
Is there anything you can do to a MDE file to prevent users from chnaging/modfying queries or tables? I wrote some of my reports, etc. based on queries and am worried that they might accidently alter the query. Any advice how others have handles this? It is possible to block people with Access Security, though this is probably overkill. Also, I can't recommend that approach, since MS has removed it from the new file format in A2007. One simple idea is to use attached tables. This has lots of advantages, one being that users can't just change the table: they have to figure out w...

Query using list of parameters
Is it possible to limit query results by a list of data or an aray rather than a single parameter? You may want to import the entire table into excel and then use Advance filters in Excel to filter the table. A query uses SQL language which Microsoft call the command Text part of the query. I'm not an expert on SQL but know a lot about SQL language. I haven't seen any way of automatically getting data from a table but somebody else may be able to answer that part of your question. A SQL statement can be very long and contain multiple WHERE phases. If you were to crea...

Reference a Date and Time in Query
Hello, I am trying to use a query to filter the table results by Date and Time. The table data is in the format: mm/dd/yyyy HH:MM:SS *M I already have a query that selects the data between two dates. But I also want to be able to select two dates and two times. Such as Between 3/15/2007 9:00 AM and 3/15/2007 10:00 AM, or use two different dates and times, etc. I have gotten close by using: Between ([Forms]![RC_WithTime]![Calendar1]+TimeSerial(9,0,0)) And ([Forms]![RC_WithTime]![Calendar2]+TimeSerial(10,0,0)) but as soon as I try to convert the TimeSerial to link to forms it says that the...

Query Design Help Needed
I am having difficulty designing a query and would like some help. The query should return only ONE row per SalesOrder. The query should SUM the receveibles from a table Receivables where SalesOrderNo is the foreignkey linking to Parent SalesOrder table and the receivable was paid within a specified date range. Currently I am getting a row for EACH receivable within a date range. Payables are summed regardless of the date range. Just fetch all costs for the given SalesOrderNo. Any help? Do I need to be more specific? Thanks. Dave It would help if you posted the SQL of the quer...

Query join Date\Time confusion
Hi I have a table that is produced from a download from a Index telephone switch. It stores the date and time data in one column in the format: 07/12/2007 08:42:23 The switch also produces an agent table tha shows their login, log off, free, busy status. However this stores just the Time in 08:42:23 Format Now I need to list the agent status against the long date time format. However I join the two tables by the date columns but access doesnt match due to the Date being on the left column. I've tried various methods, I.e. creating a query that looks like this Date: =date() ...

Proc cache consumed with one-time queries
In investigating memory usage os our SQL Server 2005 SP3, I noticed that about 4.3GB of our 32GB phsyical memory was consumed by the procedure cache. In looking at what was in the cache, I found that the overwhelming majority of the stored plans were from queries that will never be reused. In fact 4.1GB of the 4.3GB were plans with usecounts=1. Most of these turn out to be statements generated by Sql Server itself: there are many that are statements generated by SqlAgent, running scheduled tasks. Many more are the backup statements generated for log shipping. In these cases, ...

combo box query 03-20-07
I have a form containing all the books in a bookstore what I want todo is to place a combo box on the form which lists the book categoriesi.e. Science, Language, Travel so that when the user clicks onLanguage for instance then all the books that appear in the Languagcategory appearany suggestions?? "Raza" <asifhashmani@hotmail.com> wrote in message news:1174401245.665328.225930@b75g2000hsg.googlegroups.com...>I have a form containing all the books in a bookstore what I want to> do is to place a combo box on the form which lists the book categories> i.e. Science, Langua...

Delete Records from Table using Query Results
I have a table named "Records" that contains a field "Customer Number". I need to delete all the records in table "Records" if the "Customer Number" is also in the table named "Transfer". Is there a simple way to accomplish this? Thank you! Use a delete query DELETE FROM Records WHERE [Customer Number] in (SELECT [Customer Number] FROM Transfer) In query design view == Add the table Records == Select the customer number into the list of fields == Select Query: Delete from the menu == Enter the criteria under the custo...

Query and view multiple projects
Assuming that I've defined a user field (text1) as "Task Type"... Can a user assigned to the executive group (or any group for that matter) perform a query/grouping like: Show me all projects where Task Type="foo" and start date is between 01/25/10 and 01/30/10. Pete H Pete, Yes and No... 1st, the task text1 field will need to be an Enterprise Text Field, and this field will need to be added to the OLAP cube. If the task has work on it, then you could at least show the start date of the task, and from that you could get the list of projects. Ano...

Can publisher 2000 merge to an access 2000 query.
I have a database which is in Access 2000 format, and I was trying to pull information from it into Publisher 2000. I keep getting an error that goes something like " Publisher cannot open the file, it may be damaged or in a format Publisher does not understand" The mdb file opens fine in Access 2000 and in XP version (which is on another PC), and is definitely in Access 2000 format. Another mdb on my computer will open and work properly with Publisher, but the info I want is not in that one, this leads me to think that there must be something wrong with the database? Any sugg...

How change table name including references in forms and queries?
Is there a way to change a table name including references in dependent forms and queries? It looks like I would have to redesign the dependent forms using the new table name. Products to document the database and do a global find and replace. All have Access 97 and later versions. Some don't have an Access 2003 version, but may still work with Access 2003. Shareware (Try and Buy): Find and Replace http://www.rickworld.com Commercial (Try and Buy) Speed Ferret http://www.moshannon.com Commercial Total Access Analyzer http://www.fmsinc.com Free (For...

help with query 03-19-10
sql server 2008 sp1 I can't seem to get this query right. I need a way to read a table and for each person, show the person's highest value and the person's last entered value to get a result set like this person_id high last ------------- ----- ----- 1 40 20 2 30 15 here is a sample schema create table test (person_id int, enter_value int, enter_date datetime) insert into test values (1, 30, '2001-01-01') insert into test values (1, 40, '2002-01-01') insert into test values (1, 10, '...

combobox in query
Hello everybody, I have a question concerning the "Enter Parameter Value" window that opens when I run a query. Is it possible to have that as a combobox so that a list of possibilities is shown? Another question has to do with the same query. This asks a date from and to (in the query criteria >[from] And [to]. I would like the selected period to be shown in the report. Is this possible (and if so... how can I achieve that)? Thanks for all the help you can give -- Lisa Save the Dogs Onlus - www.savethedogs.eu THINK BEFORE YOU PRINT: before printing this e-mail think whe...