Ehsan Ghanbari

Experience, DotNet, Solutions

How to query over Icollection<> of a type with linq

When you are implementing many to many relations in SQL, in the case of using entity framework code fist you must use Icollection<> or Ilist<> of an entity. Imagine that you have this classes, an Author class which has a collection of books

 

    public class Author
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        public ICollection<Books> Books { get; set; }
    }

 

And the Books which has a collection of Authors

 

    public class Books
    {
        public int Id { get; set; }
        public string Title { get; set; }

        public ICollection<Author> Authors { get; set; }
    }

 

And this is your context class

 

    public class ContextClass : DbContext
    {
        public DbSet<Books> Books { get; set; }
        public DbSet<Author> Authors { get; set; }
    }

 

If you want you to create a repository class, to get the books by authors Id, You should act like this

 

    public class Repository
    {
        private ContextClass _contextClass = new ContextClass();
        public IEnumerable<Books> FindBookByAuthor(int authorId)
        {
            var query = _contextClass.Books.Where(b => b.Authors.Any(a => a.Id == authorId));
            return query.ToList();
        }
    }

 

In the lambda expression, you have to use any(), because you have a collection of authors in book class. That's all!

 



Introducing sqlbulktools

In my previews blog post I wrote about Z.bulkOperation. I migrated to sqlbulktools just because it's free with the same ability! (to be frank). Now to see the reason for my migration, fire up visual studio and Install the latest version of the library using the following command in console package manager:

 static void Main(string[] args)

        {



            var list = new List<User>();



            for (int i = 0; i < 100000; i++)

            {

                list.Add(new User

                {

                    Id = Guid.NewGuid(),

                    Name = "Ehsan",

                    Email = "[email protected]"

                });

               

            }



            Insert(list);



            Update(list);



            Delete(list);



        }



        public static void Insert(IList<User> users )

        {

            var bulkOperation = new BulkOperations();



            using (var trans = new TransactionScope())

            {

                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyContext"].ConnectionString))

                {

                    bulkOperation.Setup<User>()

                        .ForCollection(users)

                        .WithTable("Users")

                        .AddAllColumns()

                        .BulkInsert()

                       // .SetIdentityColumn(x => x.Id, ColumnDirection.Output)

                        .Commit(connection);

                }



                trans.Complete();

            }

        }



        public static void Update(IList<User> users)

        {

            var bulkOperation = new BulkOperations();



            using (var trans = new TransactionScope())

            {

                using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyContext"].ConnectionString))

                {

                    bulkOperation.Setup<User>()

                        .ForCollection(users)

                        .WithTable("Users")

                        .AddAllColumns()

                        .BulkUpdate()

                        .MatchTargetOn(x => x.Id)

                        .Commit(conn);

                }



                trans.Complete();

            }

        }



        public static void Delete(List<User> list)

        {

            var bulkOperation = new BulkOperations();



            using (var trans = new TransactionScope())

            {

                using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyContext"].ConnectionString))

                {

                    bulkOperation.Setup<User>()

                        .ForCollection(list)

                        .WithTable("Users")

                        .AddColumn(x => x.Id)

                        .BulkDelete()

                        .MatchTargetOn(x => x.Id)

                        .Commit(conn);

                }



                trans.Complete();

            }

        }

Install-Package sqlbulktools

And get started!

Simply copy and paste the above code in your console application and run it. The result of CRUD operation with sqlbulktools  on 100000 of records in my machine would be:

insert   5,066ms

update   12,372ms

delete   2,0305ms

It could be different in every machine. But I think it's a good idea to use it in an enterprise application. Cheers!



Fluent Nhibernate Sample Project in Asp.net MVC

NHibernate is an ORM designed for Microsoft.Net, it's free and open source and it's a port of Java mapper to Dot Net. And fluent NHibernate is a separate version of NHibernate which lets you write your entities according to your business instead of starting from creating tables, Fluent NHibernate is just a fluent API for mapping classes with NHibernate. what an ORM does, is persisting object In database and then retrieving when it is needed, indeed it translates the object to the database and vice versa.

In Entity Framework, you use DbContext or objectContext(in earlier versions) to make configuration, in this case, Models or properties acts as a UnitOfWork pattern but In NHibernate by Using ISessionFactory Models are keep in a separate in-memory database. I'm going to show a simple NHibernate configuration in this article, Now let's start!

create a new MVC4 project and get the latest version of Fluent NHibernate by Nuget:

NH

Or you can go to the website and get the latest version and reference the Fluent NHibernate, now create a new class named Product : 

 

    public class Product
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual Category Category { get; set; }
    }

 

 and then add the Category Class to your Model folder in your project :

 

    public class Category
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

 

you should define the properties as virtual because of lazy loading(retrieving an object or so-called data from the database when it's needed is lazy loading) , now create two separate class for mapping this Model classes via Fluent NHibernate :

 

    public sealed class ProductMapping : ClassMap<Product>
    {
        public ProductMapping()
        {
            LazyLoad();
            Table("Product");
            Id(p => p.Id);
            Id(p => p.Id).GeneratedBy.Identity();
            Map(p => p.Name);
            References(p => p.Category);
        }
    }

    public sealed class CategoryMapping : ClassMap<Category>
    {
        public CategoryMapping()
        {
            LazyLoad();
            Table("Category");
            Id(c => c.Id);
            Id(c => c.Id).GeneratedBy.Identity();
            Map(c => c.Name);
        }
     }

 

Now the most important and goal of this article is about configuration class of Fluent NHibernate, create the class with this definition

 

    public class SessionFactory : IHttpModule
    {
        private static readonly ISessionFactory _SessionFactory;
        static SessionFactory()
        {
            _SessionFactory = CreateSessionFactory();
        }
        public void Init(HttpApplication context)
        {
            context.BeginRequest += BeginRequest;
            context.EndRequest += EndRequest;
        }

        public void Dispose()
        {
        }

        public static ISession GetCurrentSession()
        {
            return _SessionFactory.GetCurrentSession();
        }

        private static void BeginRequest(object sender, EventArgs e)
        {
            ISession session = _SessionFactory.OpenSession();
            session.BeginTransaction();
            CurrentSessionContext.Bind(session);
        }

        private static void EndRequest(object sender, EventArgs e)
        {
            ISession session = CurrentSessionContext.Unbind(_SessionFactory);
            if (session == null) return;
            try
            {
                session.Transaction.Commit();
            }
            catch (Exception)
            {
                session.Transaction.Rollback();
            }
            finally
            {
                session.Close();
                session.Dispose();
            }
        }

        private static ISessionFactory CreateSessionFactory()
        {
            return Fluently.Configure()
                           .Database(MsSqlConfiguration.MsSql2008.ConnectionString(c =>    c.FromConnectionStringWithKey("DefaultConnection")))
                           .Mappings(m => m.AutoMappings.Add(CreateMappings()))
                           .CurrentSessionContext<WebSessionContext>()
                           .BuildSessionFactory();
        }

        private static AutoPersistenceModel CreateMappings()
        {
            return AutoMap
                .Assembly(System.Reflection.Assembly.GetCallingAssembly())
                .Where(t => t.Namespace != null && t.Namespace.EndsWith("Models"))
                .Conventions.Setup(c => c.Add(DefaultCascade.SaveUpdate()));
        }
    }

 

Now Open Web.config file and set the connectionString tag :

 

<connectionStrings>
    <add name="DefaultConnection" connectionString="Server=EHSAN\EHSAN;Database=NhMvcDB;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

 

Notice that NhMvcDB is my database name and DefaultConnection is my connectionString Name and EHSAN\EHSAN is my SQL server instance name!

You should create the Repository class to talk to the database, to make it simple I just create the ProductRepository Class (Not a Generic class for using all entities), to do that add an Interface to your Model :

 

    public interface IProductRepository
    {
        void Add(Product product);
        Product Get(int id);
        void Remove(Product product);
        IEnumerable<Product> GetAll();
        IEnumerable<Product> GetAllProductsByCategoryQuery(int categoryId);
        IEnumerable<Category> GetAllCategoriesQuery();
    }

 

And then  implement the members in ProductRepository class like this

 

    public class ProductRepository : IProductRepository
    {
        private readonly ISession _session;

        public ProductRepository()
        {
            _session = SessionFactory.GetCurrentSession();
        }
        public void Add(Product product)
        {
            SessionFactory.GetCurrentSession().Save(product);
        }

        public Product Get(int id)
        {
            return SessionFactory.GetCurrentSession().Get<Product>(id);
        }

        public void Remove(Product product)
        {
            SessionFactory.GetCurrentSession().Delete(product);
        }

        public IEnumerable<Product> GetAll()
        {
            return _session.Query<Product>();
        }

        public IEnumerable<Product> GetAllProductsByCategoryQuery(int categoryId)
        {
            var products= _session.Query<Product>();
            var productsByCategory = from c in products
                                     where c.Id == categoryId
                                     select c;
            return productsByCategory;
        }

        public IEnumerable<Category> GetAllCategoriesQuery()
        {
            return _session.Query<Category>();
        }
    }

 

Now create the Service interface

 

    public interface IProductService
    {
        void AddProduct(Product product);
        void RemoveProduct(Product productId);
        Product GetProduct(int productId);
        IEnumerable<Product> GetAllProducts();
        IEnumerable<Product> GetAllProductsByCategory(int categoryId);
        IEnumerable<Category> GetAllCategory();
    }

 

And Service implementation :

 

    public class ProductService : IProductService
    {
        private readonly IProductRepository _productRepository;
        public ProductService(IProductRepository productService)
        {
            _productRepository = productService;
        }
        public void AddProduct(Product product)
        {
            _productRepository.Add(product);
        }
        public void RemoveProduct(Product productId)
        {
            _productRepository.Remove(productId);
        }
        public Product GetProduct(int productId)
        {
            var product = new Product { Id = productId };
            return product;
        }
        public IEnumerable<Product> GetAllProducts()
        {
            var products = _productRepository.GetAll();
            return products;
        }
        public IEnumerable<Product> GetAllProductsByCategory(int categoryId)
        {
            var products = _productRepository.GetAllProductsByCategoryQuery(categoryId);
            return products;
        }
        public IEnumerable<Category> GetAllCategory()
        {
            var categories = _productRepository.GetAllCategoriesQuery();
            return categories;
        }
    }
 

 and in Controllers folder create the ProductController with this definition

 

    public class ProductController : Controller
    {
      private readonly IProductService _productService;

        public ProductController(IProductService productService)
        {
            _productService = productService;
        }

        public ActionResult List()
        {
            var products = _productService.GetAllProducts();
            return View(products);
        }

        public ActionResult Add()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Add(Product product)
        {
            if (ModelState.IsValid)
            {
                _productService.AddProduct(product);
            }
            return View();
        }
  }

 

Which list action returns the list of products and by Add action, you can add a product, about the Dependency Resolver, add Ninject by Nuget :

 

Nuget

 

Then add this configuration class to your project

 

    public class NinjectConfig
    {
        private static IKernel _ninjectKernel;
        public class NinjectDependencyResolver : DefaultControllerFactory
        {
            public NinjectDependencyResolver()
            {
                _ninjectKernel = new StandardKernel();
                ConfigurDepndency();
            }
            protected override IController GetControllerInstance(RequestContext requestContext, Type controllerType)
            {
                return controllerType == null ? null : (IController)_ninjectKernel.Get(controllerType);
            }
        }
        private static void ConfigurDepndency()
        {
            _ninjectKernel.Bind<IProductRepository>().To<ProductRepository>();
            _ninjectKernel.Bind<IProductService>().To<ProductService>();
        }
    }

 

And add this line of code to your global.asax in Application_Start()

 

 ControllerBuilder.Current.SetControllerFactory(new NinjectConfig.NinjectDependencyResolver()); 

 

 end!



Layer Supertype pattern with example in C#

First of all look at the definition by @martinfowler "A type that acts as the supertype for all types in its layer" all you need is a Supertype Class for all the Objects. I usually use it in the Repository layer as a generic Repository. I recommend to use it if you have more than one kind of object in your layer. Let's consider an example in the real world, Create a class and name it Profile

 

   public class Profile
    {
        public string Name { get; set; }
        public int Age { get; set; }
    }

 

 Then create a generic interface called IRepository

 

 public interface IRepository<T>
    {
        void Add(T entity);
        void Delete(T entity);
    }

 

Now create the IProfileRepository

 

    public interface IProfileRepository : IRepository<Profile>
    {
    }

 

Then create the Repository Class, this is the Generic Class that we talk about

 

    public class Repository<T> : IRepository<T>
    {
        public void Add(T entity)
        {
            throw new NotImplementedException();
        }

        public void Delete(T entity)
        {
            throw new NotImplementedException();
        }
    }

 

As you see you have to implement the Add and Delete methods, now if you want to create the ProfileRepository you don’t need to create the Delete and Add again

 

    public class ProfileRepository : Repository<Profile>, IProfileRepository
    {
    }

 

 That's it, Thanks for reading!



Difference between layer super type and template method patterns

About two days ago I asked a question about the differences between these two patterns. after reading about the template method patterns in detail I found that although there are lots of similarities between them layer supertype is not the same as method template and vice versa. I've blogged about Layer Supertype before in here, now I want to talk about method template pattern. Basically, the template method is to run common functionality of a base class from the child class and use the objects of the child type in the common method functionality of the base type. It means that the best way of implementing the pattern is to create a base abstract class with abstract methods to implement the implementer of the base abstract class members.  Take a look this sample in C#:

 

 public abstract class Order
    {
        protected abstract bool CheckAccountAmount();
        protected abstract bool CheckProductAvailibility();

        public bool MakeOrder()
        {
            if (CheckAccountAmount())
            {
                if (CheckProductAvailibility())
                {
                    //Do Order process
                }
                else
                {
                    return false;
                }
                return false;
            }
            return false;
        }
    }


    public class Customer : Order
    {
        protected override bool CheckAccountAmount()
        {
            // recieve the information from credit card servers
            return true;
        }

        protected override bool CheckProductAvailibility()
        {
            // go to the database to see if the product is available
            return true;
        }
    }


    public class Member : Order
    {
        protected override bool CheckAccountAmount()
        {
            // go to the database to check if the members has enough account in his/her account
            return true;
        }

        protected override bool CheckProductAvailibility()
        {
            //  // go to the database to see if the product is available
            return true;
        }
    }


    public class Presentation
    {
        public void Operatio()
        {
            //call customer methods for order
            Order customer = new Customer();
            customer.MakeOrder();

            //call member methods for order
            Order member = new Member();
            member.MakeOrder();
        }
    }

 

As you can see, there is some common operation in both Member and Customer, I've created the MakeOrder method in the base class to be available for member and Customer. Making Order is a common task for Member and Customer on creating order and each of them can use it for its own order process. Making order is just for making an order and works only for it! I mean that It's a common task for making an order in the system but what do you think about creating a basket for customers and members? Can you use the same method (MakeOrder)? you should create another base class for it yeah?

the main goal of this blog post is to define the differences between template method pattern and layer supertype. If you have a look at my post about the layer supertype you can see that there are more similarities between them. Both of them reduce the duplication and reimplementation of code. I agree with Adriano comment on my question, it's better to talk about the similarities than differences of these patterns. But I think that template method patterns works on the same implementation of subtypes and redefine certain steps of an algorithm without changing the Algorithm's Structure. And it's mostly about sequential and common processes but layer supertype works on similarities of some operation on types and it's A type that acts as the supertype for all types in its layer.



About Me

Ehsan Ghanbari

Hi! my name is Ehsan. I'm a developer, passionate technologist, and fan of clean code. I'm interested in enterprise and large-scale applications architecture and design patterns and I'm spending a lot of my time on architecture subject. Since 2008, I've been as a developer for companies and organizations and I've been focusing on Microsoft ecosystem all the time. During the&nb Read More

Post Tags
Pending Blog Posts
Strategic design
Factory Pattern
time out pattern in ajax
Selectors in Jquery
using Log4net in asp.net MVC4
How to use PagedList In asp.net MVC
Redis as a cache server
Domain driven design VS model driven architecture
What's the DDD-lite?
Multiple submit buttons in asp.net MVC