Linq to SQL c# Composite Key Help


I need help implementing a table with a composite key in a repository system which is using  a generic structure.

I basically have a many to many relationship. For example, with the tables Centre, User, CentreUser

CenterUser has a composite key UserID and CentreID.

The repository uses a generic IRepository to provide Insert, Delete as a standard.

    public interface IRepository<T>
        int Insert(T entity);
        void Update(T entity);
        void Delete(T entity);
        IQueryable<T> SearchFor(Expression<Func<T, bool>> predicate);
        IQueryable<T> GetAll();
        IOrderedEnumerable<T> GetPage(int pageIndex, int pageSize, string sortBy, string sortDirection);
        T GetById(int id);

Open in new window

where the repositorty class is

public class Repository<T> : IRepository<T> where T : class, IEntity
        protected Table<T> DataTable;

        public Repository(DBDataContext dataContext)
            DataTable = dataContext.GetTable<T>();
        public Repository(DataContext dataContext)
            DataTable = dataContext.GetTable<T>();

        #region IRepository<T> Members

        public int Insert(T entity)
            return entity.ID;

        public void Update(T entity)
            DataTable.Attach(entity, true);

        public void Delete(T entity)

        public IQueryable<T> SearchFor(Expression<Func<T, bool>> predicate)
            return DataTable.Where(predicate);

        public IQueryable<T> GetAll()
            return DataTable;

        public IOrderedEnumerable<T> GetPage(int pageIndex = 0, int pageSize = 10, string sortBy = "ID", string sortDirection = "asc")
            var param = Expression.Parameter(typeof(T));

            var sortExpression = Expression.Lambda<Func<T, object>>
                (Expression.Convert(Expression.Property(param, sortBy), typeof(object)), param).Compile();

            var results = DataTable.Skip((pageIndex - 1)*pageSize).Take(pageSize);

            switch (sortDirection.ToLower())
                case "asc":
                    return results.OrderBy(sortExpression);
                    return results.OrderByDescending(sortExpression);

        public T GetById(int id)
            return DataTable.Single(e => e.ID.Equals(id));


Open in new window

However each class in the DBML inherits from an IEntity interface which states the class must have a ID. i.e.

    public interface IEntity
        int ID { get; }

Open in new window

In the class system is it possible to override and maybe say ID = UserID + CentreID?

Or any other implementation I can use?

Any help would be appreciated.

Thanks in Advance,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
I would think that you can't do UserID + CentreID, since these are integers, and that would be a mathematical addition.  I would think that you should have a primary key in the table that is CenterUserId.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ioannis ParaskevopoulosCommented:
I think you should have three columns. The ID, the UserID and the CentreID.

flynnyAuthor Commented:
Hi Guys,

Thanks for this I thought this may be the case.

I've gone down the entity framework route now and scaffolded out from the existing db. Handles the many to many well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.