I am trying to dynamically select a table in linq. Is this possible?
For example, I have 3 tables - Country, City and World and World has 1-many mapping with COuntry and City. Both Country and City have same fields (same name and type). Note, this is an example and I need both Country and City tables.
So, I am now trying,
var query = from c in dc.Worlds
from d in dc.Countries
In second line, from d in dc.Country, is it possible to dynamically choose dc.Countries or dc.Cities based on query string, for example.
If your one country maps to one City instance, I think it will automatically include the navigator of "City" in each entity model of Country. So you can do this:
var query = from c in dc.Worlds
from d in dc.Countries
select new
{
XXProperty = d.Cities
};
//Use foreach to loop each query and city.
Hi, I don't want to fetch related things from B and C and they are not related. I want to fetch related things from A and B in one scenario and from A and C in another scenario.
Imagine my table A is a product table which has many fields.
Table B is a segment table which has fields - ID, Name, Active. (Segments for customer segmentation)
Table C is a category table and has fields - ID, Name, Active. (Category is internal category for products)
There is no business reason for Table B and Table C to be related.
So, now if I want to do a browse feature - browse by category and browse by segments. To do so, I will write a page browse.aspx and pass in relevant query string. For example, browse.aspx?segmentid=1 will browse segments and browse.aspx?categoryid=1 will browse
categories.
So, now I am writing the method for browsing by segment
var query = from c in dc.products
from d in dc.Segments
where c.SegmentId == d.Id
select c;
and a very similar method for browsing by categories
var query = from c in dc.products
from d in dc.Categories
where c.CategoryId == d.Id
select c;
Now, if I have 20 more browsing options - I will be writing very similar code 20 times!
So, this is what I am trying to avoid. Hopefully, this is making sense now.
shahed.kazi
All-Star
17953 Points
3635 Posts
Dynamically choose table in Linq to Sql
Nov 27, 2012 12:04 AM|LINK
I am trying to dynamically select a table in linq. Is this possible?
For example, I have 3 tables - Country, City and World and World has 1-many mapping with COuntry and City. Both Country and City have same fields (same name and type). Note, this is an example and I need both Country and City tables.
So, I am now trying,
var query = from c in dc.Worlds from d in dc.CountriesIn second line, from d in dc.Country, is it possible to dynamically choose dc.Countries or dc.Cities based on query string, for example.
Any help much appreciated.
.NET World |Captcha Control
raju_mab
Member
559 Points
110 Posts
Re: Dynamically choose table in Linq to Sql
Nov 27, 2012 07:20 AM|LINK
Please have a look..
http://www.c-sharpcorner.com/uploadfile/scottlysle/generic-data-access-using-linq-to-sql-and-C-Sharp/
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Dynamically choose table in Linq to Sql
Nov 28, 2012 12:22 AM|LINK
Hello,
If your one country maps to one City instance, I think it will automatically include the navigator of "City" in each entity model of Country. So you can do this:
var query = from c in dc.Worlds from d in dc.Countries select new { XXProperty = d.Cities }; //Use foreach to loop each query and city.shahed.kazi
All-Star
17953 Points
3635 Posts
Re: Dynamically choose table in Linq to Sql
Nov 28, 2012 04:36 AM|LINK
Thanks Decker,
Country and City are not related - I just used it as an example. Both Country and City got 1-many mapping with World.
.NET World |Captcha Control
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Dynamically choose table in Linq to Sql
Nov 28, 2012 04:41 AM|LINK
Then what's the relationship between Country and City? Can you show us your Diagram?
I suggest you creating a relationship between the two tables, if you wanna fetch related result.
shahed.kazi
All-Star
17953 Points
3635 Posts
Re: Dynamically choose table in Linq to Sql
Nov 28, 2012 09:51 PM|LINK
There is no relation between Country and City. If you read my question, I am stating this as an example. You can consider 3 tables A, B, and C
A-B > 1 to many
A-C > 1 to many
B-C > no relation
But both B and C has same table structure with same field names.
.NET World |Captcha Control
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Dynamically choose table in Linq to Sql
Nov 29, 2012 12:15 AM|LINK
Hi again,
I feel very strange——Since you wanna fetch related things from B or C……So why B and C don't have any relationships between each other?
And what's more, why do you design B and C to the same structure?
Can you give us the full reason maybe we can simply that?
Reguard & Thanks!
shahed.kazi
All-Star
17953 Points
3635 Posts
Re: Dynamically choose table in Linq to Sql
Nov 29, 2012 01:18 AM|LINK
Hi, I don't want to fetch related things from B and C and they are not related. I want to fetch related things from A and B in one scenario and from A and C in another scenario.
Imagine my table A is a product table which has many fields.
Table B is a segment table which has fields - ID, Name, Active. (Segments for customer segmentation)
Table C is a category table and has fields - ID, Name, Active. (Category is internal category for products)
There is no business reason for Table B and Table C to be related.
So, now if I want to do a browse feature - browse by category and browse by segments. To do so, I will write a page browse.aspx and pass in relevant query string. For example, browse.aspx?segmentid=1 will browse segments and browse.aspx?categoryid=1 will browse categories.
So, now I am writing the method for browsing by segment
var query = from c in dc.products
from d in dc.Segments
where c.SegmentId == d.Id
select c;
and a very similar method for browsing by categories
var query = from c in dc.products
from d in dc.Categories
where c.CategoryId == d.Id
select c;
Now, if I have 20 more browsing options - I will be writing very similar code 20 times!
So, this is what I am trying to avoid. Hopefully, this is making sense now.
.NET World |Captcha Control
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Dynamically choose table in Linq to Sql
Nov 29, 2012 01:28 AM|LINK
Hello again,
Now I come to understand what you mean——There are two choices for you:
1) Use CreateQuery——Because this needs a string as its parameter, but the string can be dynamically combined to create a new one.
You can refer this: http://msdn.microsoft.com/en-us/library/bb339670.aspx
2) Use a 3-rd party tool called Dynamic Linq:
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
Attention: Both of the two ways should be applied in LINQ-TO-ENTITY. For linq-to-sql, no direct way yet, I'm afraid.