Linq Query in a Query

Question

Hello I have the following:

Table Agents has: UserId, Name, Address, City, etc. (this table has general information of an agent).
Table Zips has: UserId, ZipCodes (this field is a VarMax and has up to 10 zip codes separated by commas as: 92342, 92312, 92322, etc.) these zip codes are the locations an agent services.

I also have this array:
string[] zips1 = {"92342", "92533", "92522", "92023", "92643" }; //I need to find all agents that service these zip codes

I have this code so far:

var agents = (from a in
 db.Agents where allzips.Contains(***)
 orderby a.Approved ascending
select new
 { a.FirstName, a.LastName, a.OfficeName, a.OfficeAddress, a.OfficeCity, a.OfficeZip, a.OfficeState, a.OfficePhone, a.UserId }).Distinct();

I need to know what code I can have instead of *** in the above query. In other words *** needs to call Zips.ZipCodes where a.UserId == Zips.UserId and the ZipCodes need to be in zips1

I hope this makes sense, any help will be greatly appreciated.

Louis

Answer 1

Hi Louis,

here we have got 2 tables and an array of zip codes. u want to find those agents who services zip codes from zip1.
column 'userid' is the only column which relates agents & zips.

solution :

u have to use join in ur query. Since i am a vb.net guy, i hv written the query in vb. Hope this works for u. Best of luck.

dim Drows as IEnumerable( of DataRow)  =  from c in db.Tables("Agents") _
                                                                 Join d in dz.Tables("Zips") on c!userid
                                                                 Equals d.Field(of String)("userid")
                                                                 where zips1.Contains (d!zipcodes) _
                                                                 select c       

regards,
vaibhav.

Answer 2

Hi Vaibhav,

Thanks for your reply, this is how I translated your query to C#:

IEnumerable<DataRow> agents = from c in db.AgentProfile1s
      join d in db.AgentZips on c.UserId equals d.UserId
      where allzips.Contains(d.Zips)
      select c;

But I get the following error:

Cannot implicitly convert type 'System.Linq.Iqueryable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Data.DataRow>'. An explicit conversion exists (are you missing a cast?)

The problem seems to be in the join statement, but I am not sure, any ideas?

Thank you,

Louis

Answer 3

Replace <DataRow> with whatever is the type name of "c" (i.e. the entity type of AgentProfile1s).

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 4

Hi Louis,

As far as i know in C# linq query, u have to use AsEnumerable() after table name,
 e.g.
 db.AgentProfile1s.AsEnumerable() and db.AgentZips.AsEnumerable()
well i completely agree with kristofer, or simply use var agents .
do reply if u get it properly else i will post the code for u in C#.

regard,
Vaibhav.

Answer 5

Thank you,

I have it like this now:

IEnumerable<AgentProfile1> agents = from c in db.AgentProfile1s.AsEnumerable()
                                    join d in db.AgentZips on c.UserId equals d.UserId
                                    where allzips.Contains(d.Zips)
                                    select c;

But I am still getting the same error.

I have also tried IEnumerable<Table> and same error message.

Any other suggestions?

Thanks,

Louis

Answer 6

Hi Louis,

here i have posted c# code for u, Plz give it a try and reply if it works for u.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;



 DataTable ds = new DataTable();
        ds.TableName =   "agents";
        ds.Columns.Add("userid");
        ds.Columns.Add("name");
        ds.Columns.Add("city");
        ds.Rows.Add("1", "vaibhav", "india");
        ds.Rows.Add("2", "louis", "us");
        ds.Rows.Add("3", "micheal", "canada");
        ds.Rows.Add("4", "louis1", "australia");

        DataTable dp = new DataTable();
        dp.TableName = "zips";
        dp.Columns.Add("userid");
        dp.Columns.Add("zipcodes");
        dp.Rows.Add("1", "400706");
        dp.Rows.Add("2", "400707");
        dp.Rows.Add("3", "400708");
        dp.Rows.Add("4", "400709");

            string[] zips1  = {"400706", "400707"};


       
            IEnumerable<DataRow> dc = from c in ds.AsEnumerable()
                     join d in dp.AsEnumerable()
                     on c.Field<string>("userid") equals d.Field<string>("userid")
                     where zips1.Contains(d.Field<string>("zipcodes"))
                     select c;

Answer 7

Thank you,

I have it like this now:

IEnumerable<AgentProfile1> agents = from c in db.AgentProfile1s.AsEnumerable()

                                    join d in db.AgentZips on c.UserId equals d.UserId

                                    where allzips.Contains(d.Zips)

                                    select c;

But I am still getting the same error.

I have also tried IEnumerable<Table> and same error message.

Any other suggestions?

Thanks,

Louis

Don't do .AsEnumerable inside the query; that will force client side evaluations. Instead, change to:

IEnumerable<AgentProfile1> agents = (from c in db.AgentProfile1s
                                    join d in db.AgentZips on c.UserId equals d.UserId
                                    where allzips.Contains(d.Zips)
                                    select c).AsEnumerable();

...if you want an enumerable.

...or IQueryable<AgentProfile1> agents = from ... select c; if you want a query.

...or List<AgentProfile1> agents = (from ... select c).ToList(); if you want a list.

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 8

Hi I have rewritten my code as follow:

 var agents = from a in db.AgentProfile1s<br/>
              from z in db.AgentZips<br/>
              from v in allzips<br/>
              where a.OfficeZip.Contains(v) || a.UserId == z.UserId && z.Zips.Contains(v)<br/>
              select new { a.FirstName, a.LastName, a.OfficeName };

I'll be using 'agents' it in a ListView.  My question is if this is well optimized or do I need to use IEnumerable or something else.

Thanks for your advice.

Louis

Answer 9

I've made another modification to my code, namely:

var agents = (from a in db.AgentProfile1s
              from z in db.AgentZips
              from v in allzips
              where a.OfficeZip.Contains(v) || a.UserId == z.UserId && z.Zips.Contains(v)
              orderby a.Approved descending, v
              select new { a.FirstName, a.LastName, a.OfficeName, a.OfficeAddress, a.OfficeCity, <br/>
                          a.OfficeState, a.OfficeZip, a.OfficePhone, a.Email1 }).Take(9);

But I get this error message:

 Expanding the Results View will enumerate the IEnumerable

Please help. Thank you,

Louis