views:

204

answers:

1

I have 4 tables: CustomerDocument, CustomerLink, CustomerAdditionalInfo, and CustomerImage. They each have a value of CustomerNumber, and I need to way to pull the customer numbers from each table and put in a drop down list. I know how to do it with one table, but not multiple. Also, there is a restriction that the CustomerNumber needs to be not null, so do I need to include this with each join? Here is a bit of code I have now. oDb is the DataContext

var oData = from c in oDb.CustomerAdditionalInfos
                    where ( c.CustomerID == CustomerID && 
                            c.CustomerNumber != null   && 
                            c.CategoryID == CategoryID )

                    orderby c.CustomerNumber
                    select new { c.CustomerNumber };

        return oData;
+2  A: 

You could do this....

var oData = (from c in oDb.CustomerAdditionalInfos
             where c.CustomerNumber != null
             select new 
             {
                  CustomerNumber = c.CustomerNumber
             }).Union
                 (from d in oDb.CustomerDocument
                  where d.CustomerNumber != null
                  select new
                  {
                      CustomerNumber = d.CustomerNumber
                  }).Union
                      (from l in oDb.CustomerLink
                       where l.CustomerNumber != null
                       select new
                       {
                           CustomerNumber = l.CustomerNumber
                       }).Union
                            (from i in oDb.CustomerImage
                             where i.CustomerNumber != null
                             select new
                             {
                                 CustomerNumber = i.CustomerNumber
                             }).OrderBy(c => c.CustomerNumber);

That is simply a union of all of the CustomerNumbers in all four tables. This WILL include duplicates if there are duplicates. If you want only distinct CustomerNumbers, then just do a Distinct() after the OrderBy.

Eclipsed4utoo
Thanks much, this worked perfectly. How would I keep track of which table the customer number came from when the user selects it from the ddl?
Justen
You could add a second field in the `select new` which would be the table name. Then you would assign the CustomerNumber as the `DataTextField`, and the "TableName" as the 'DataValueField'.
Eclipsed4utoo
Ah okay, thanks a lot, very helpful. Just out of curiosity, what if someone wanted to keep track of the table they came from, + another element? For example, if their were duplicate numbers (but different values) in a table, and you wanted to load the ddl with the numbers, and keep track of the ID and table name of that number, how would one do so?
Justen
The value field can be anything you want it to be. It could be a comma-delimited string of values. As long as you know what the value is and can parse it when the selection has been chosen, then it doesn't really matter what you put there. And do add documentation for the future. You could make the value of the item in the dropdownlist to be "TableName,IDFromTable,CustomerID", and you can parse that since you know what the format is.
Eclipsed4utoo
Ah okay I get it. Again, thanks for you help
Justen