Linq GetTable< > return empty rows

Question

I have a very strange problem. I followed an MVC tutorial.

I have an interface:

namespace DomainModel.Abstract
{
    public interface IProductsRepository
    {
        IQueryable<Product> Products { get; }
    }
}

A repository inherits that interface:

namespace DomainModel.Concrete
{
    public class SqlProductsRepository : IProductsRepository
    {
        private Table<Product> productsTable;
        public SqlProductsRepository(string connectionString)
        {
            try
            {
                productsTable = (new DataContext(connectionString)).GetTable<Product>();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
            }
        }
        public IQueryable<Product> Products
        {
            get { return productsTable; }
        }
    }
}

My Product class looks like this:

namespace DomainModel.Entities
{
    [Table(Name = "Products")]
    public class Product
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        public int ProductID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }       
        public decimal Price { get; set; }

    }
}

And my controller looks like this:

namespace WebUI.Controllers
{
    public class ProductsController : Controller
    {

        private IProductsRepository productsRepository;
        public ProductsController()
        {
            string connString = @"Data Source=INFO_KW-HZ7YX91;Initial Catalog=SportStore;Integrated Security=True";
            productsRepository = new SqlProductsRepository(connString);

        }
        public ViewResult List()
        {
            return View(productsRepository.Products.ToList());

        }

    }
}

My Products table has 8 rows. When I run the web site, It displays 8 rows but they are all blanks and zero. When I debugged it, I can see productsTable has 8 rows with correct column names, but the string data are null and the integer data are zero.

What did I do wrong?

Thanks

Answer 1

It has been quite a while since last time I did custom attributes like this, but I guess you need to put [Column] for all properties you need to persist. Something like:

    [Column]
    public string Name { get; set; }
    [Column]
    public string Description { get; set; }
    [Column]
    public string Category { get; set; }       
    [Column]
    public decimal Price { get; set; }

assuming that your sql table field names match the property names accordingly.

Answer 2

You didn't add [Column] attributes to any of your columns but your PK. LinqToSql is very attribute heavy, and hand-coding the classes will be fraught with risk and (most likely) frightening exceptions. You should use sqlmetal.exe or your own generator.