LINQ to SQL and Data Projection, MVC

Question

HI I have a table with some some values (IDs), and of course when i get the result i got just the int IDs, but i want to put it more user friendly, for example when its the number 1, i want to put the string "Avaible", when its 2 "Not avaible", im on an N tiers enviroment and i need to get this done on the Model, whats the best way to accomplish this, i have to declare another class to project the strings, or must i use something like a dictionary, Key -> Value.

right now i just have this

return from t in db.products where t.productID==productID select t;

Answer 1

If you are using Linq to SQL you need another table to contain product status:

Table Name: Product Status
Fields:     ProductStatusID   int Indentity Primary Key
            ProductStatus     nvarchar(50)

Add a field to your Products Table:

Field to Add:  ProductStatusID    int

Add some statuses to your new table, and set the ProductStatusID of each product to an appropriate status id.

Add a constraint that connects the two ProductStatusID fields together. The easiest way do this is to create a diagram in SQL Server Management Studio Express, drag both tables onto the diagram, and then drag the ProductStatusID field from the ProductStatus table to the Products table, and click OK on the dialog that opens.

Rebuild your Linq to SQL data classes. You do this by deleting and recreating the DBML file, and dragging your tables into the designer again.

When you get a products object (p) from your dataContext object, you should now see this:

p.ProductStatus   <-- The text description of the product's status.

Linq to SQL will reach into your ProductStatus table, and lookup the appropriate status description.