LINQ what is the best pratice to create db manager class?

What is the best performance and consistency?

Variant one (inherit from DataContext):

public class MyDbManager : DataContext
{
    public MyDbManager(string conn):base(conn)
    {
        var tblMyTable1 = GetTable<MyTable1>();
        var tblMyTable2 = GetTable<MyTable2>();
    }
    public void GetEntityById(int id)
    {
        var myEntity = (from p in tblMyTable1 where p.Id == id  select p).FirstOrDefault();
    }
}

Variant 2 (Create each time new connection)

    public class MyDbManager
    {
        public string ConnectionSt开发者_高级运维ring = "myConnStr";

        public List<Program> GetAllPrograms ()
        {
            var programs = new List<Program>();
            using (var db = new DataContext(ConnectionString))
            {
                var tblPrograms = db.GetTable<Program>();
                programs = (from p in tblPrograms select p).FirstOrDefault( .ToList());
            }
            return programs;
        }
}

Or maybe you have third variant? Thank you a lot!

---

For consistency, I would adopt the Repository Pattern and for performance I would adopt the Unit of Work pattern.

You appear to be kind of going down that route with your second approach, however, I would create a repository per entity in my database e.g. Table1Repository/Table2Repository, and delegate all responsibility for each entity to the appropriate repo....just my opinion.

If you look at your first approach, you aren't actually correctly caching the tables locally but I understand what it is you are getting at. So if you think about it, although it may be faster once your system is up & running is it really efficient (or necessary) to cache every record from a table? In most cases you would query things like Customer->Orders so surely it would make more sense to query the database if & when it is needed? As not only will your trip be shorter it will be less intensive on the server.

DataContext's are lightweight and were designed with this sort of approach in mind.