0

I need a ColumnName of a table in EF which I have it's ID. My Tables are something like this :

Retailers

[RetailerId] [int] IDENTITY(1,1) NOT NULL,
[RetailerName] [varchar](50) NOT NULL,
[StateName1] [bit] NOT NULL,
[StateName2] [bit] NOT NULL,
[StateName3] [bit] NOT NULL,

States

   [SId] [int] IDENTITY(1,1) NOT NULL,
   [StateName] [varchar](50) Not Null

I receive an SId from a function and need to select all retailers which are located in that State. Something like: 

var listOfRetailers = (from m in db.Retailers where m.[a column that it's id is equal to SId] == true select m ).toList();
Amir978
  • 857
  • 3
  • 15
  • 38
  • 1
    How do you have connection between ( referential integrity) between the two tables ? – Shyju Jun 01 '12 at 01:17
  • what are these StateName1, StateName2, StateName3 which are of type [bit]? How do you relate these two tables? – Prashanth Thurairatnam Jun 01 '12 at 01:19
  • StateName1, StateName2 and StateName3 are State Names from States Table and Reatlers table define if any retailer provides service in any of these states or not. There is nor relation between Retailers and States table but the StateName columns in Retailers are in order as they are in States( for example States = [1,ACT] , [2,NSW] , [3, NT] and retailers = [1, r1 , false , true , false ] , [2, r2 , true, false , true ] which means r1 does provide service only in NSW. r2 provides service in ACT and NT ) – Amir978 Jun 01 '12 at 01:30

1 Answers1

0

From your comments I think this is what you are looking for. But design wise what you have done is bad. I will explain shortly.

var listOfRetailers;
if(SId == 1)
{
listOfRetailers = db.Retailers.select(r=> r.StateName1.Equals(true)).ToList();
}
else if(SId == 2)
{
listOfRetailers  = db.Retailers.select(r=> r.StateName2.Equals(true)).ToList();
}
else if(SId == 3)
{
listOfRetailers  = db.Retailers.select(r=> r.StateName3.Equals(true)).ToList();
}

EDIT

Technically this is a bad design. Coz you are making the assumption that your States table will have 3 records

1 - ACT
2 - NSW
3 - NT

For every state you are have a corresponding column in Retailers table StateName1, StateName2, StateName3

Lets say you want to introduce a 4th state (say 4 - VIC). Now you will need to introduce a new column called StateName4 in your Retailers table. (by doing this there will be code level and DB level changes)

Also with the new columns you are introducing additional overheard of unused/ redundant data.

since this is a many-to-many situation (one state can have many retailers and one retailer can be in many states), the best approach would be to create 3 tables (Retailer, State, RetailerState), where by RetailerState table will play the role of mapping entries from State, Retailer tables

Retailer
---------
[RetailerId] 
[RetailerName]

State
------
[SId] 
[StateName]

RetailerState
--------------
[RetailerId] 
[SId]
Prashanth Thurairatnam
  • 4,353
  • 2
  • 14
  • 17
  • Thanks for you answer. What I'm looking for actually is a way to get a list of Columns name in a table. I've found this http://www.c-sharpcorner.com/uploadfile/dhananjaycoder/listing-columns-name-with-type-of-a-table-using-linq/ but it doesn't work on EF. Is there any any method to return table columns name in order? – Amir978 Jun 01 '12 at 02:54
  • I don't think there is a direct method to do this. This might help. http://stackoverflow.com/questions/6056597/entity-framework-how-do-i-get-the-columns – Prashanth Thurairatnam Jun 01 '12 at 08:54