How to make Linq2Sql work with my models

Question

I work on a project where we was using SqlConnection, SqlCommand and plain SQL to access repository. Now, I am trying to migrate to Linq2Sql and I want to use the same models. How can I achieve this?

I will reduce the project structure to the minimal meaningful example.
Let's say I have the following classes:

namespace Model
{
    public class User
    {
        public int Id { get; set; }
    }
}

All models in Model namespace are one-in-one copy of database entities.

namespace Repository
{
    public class UserRepository
    {
        private _sqlConnectionHelper = new SqlConnectionHelper();

        public User GetUser()
        {
            var reader = _sqlConnectionHelper
                .ExecuteAndReturnReader("SELECT * FROM [dbo].[Users]");

            while (reader.Read())
            {
                return new User
                {
                    Id = (int)reader["Id"]
                };
            }

            return null;
        }
    }
}

Now I am trying to migrate to Linq2Sql. I have created a MyContext.dmbl file with User table in Repository project. It has generated the following class:

namespace Repository
{
    [global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Users")]
    [global::System.Runtime.Serialization.DataContractAttribute()]
    public partial class User: INotifyPropertyChanging, INotifyPropertyChanged
    {
         private int _ID;

         public int ID
         {
            get
            {
                return this._ID;
            }
            set
            {
                if ((this._ID != value))
                {
                    this.OnIDChanging(value);
                    this.SendPropertyChanging();
                    this._ID = value;
                    this.SendPropertyChanged("ID");
                    this.OnIDChanged();
                }
            }
        }

        // Some other methods
    }
}

Now, the problem is that I have a lot of of entities, repositories, models etc. I don't want to change the whole project to use new generated models but not mine from Model namespace. How can I make Linq2Sql work with my models?

It also affects my architecture because in case of these models, the entity and the repository is the same object. I don't need my entities to be CRUD objects. I just want to make minimal changes to project and only use convenient LINQ requests instead of plain SQL like this:

namespace Repository
{
    public class UserRepository
    {
        private MyContextDataContext _myContext = new MyContextDataContext();

        public User GetUser()
        {
            return _myContext.Users.FirstOrDefault();
        }
    }
}

Or I just don't understand something about purpose and logic of Linq2Sql and it is how it only works like?

Of course, I can write converters or use reflection and make a copy of object property-by-property but it doesn't sound like a good solution.

score 0 · Accepted Answer · edited May 23 '17 at 12:29

Ok. Finally, I have found an answer which is pretty simple - Linq2Sql is not a library I was looking for.

There are different approaches for object-relational mapping: code-first, database-first, model-first.

Here is the good StackOverflow article about their differences.

Now, when I learned it, what I have described in my question could be easily rephrased as "how can I make Linq2Sql be code-first". The answer is simple - I cannot do this.

As a result of some investigations, I have understood that I was looking for Entity Framework which perfectly fit in my project.
Now, my repository looks like this:

namespace Repository
{
    public MyContextDataContext : DbContext 
    {
        public DbSet<User> Users { get; set; }
    }

    public class UserRepository
    {
        private MyContextDataContext _myContext = new  MyContextDataContext();

        public User GetUser()
        {
            return _myContext.Users.FirstOrDefault();
        }
    }
}

How to make Linq2Sql work with my models

1 Answers1