using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Customer
    {
        public int Id { set; get; }
        public string FirstName { set; get; }
        public string LastName { set; get; }

        public virtual AlimentaryHabits AlimentaryHabits { set; get; }
        public virtual ICollection<CustomerAlias> Aliases { get; set; }
        public virtual ICollection<Role> Roles { get; set; }
        public virtual Address Address { get; set; }
    }
}

namespace EF_Sample35.Models
{
    public class CustomerAlias
    {
        public int Id { get; set; }        
        public string Aka { get; set; }

        public virtual Customer Customer { get; set; }
    }
}

using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Role
    {
        public int Id { set; get; }
        public string Name { set; get; }

        public virtual ICollection<Customer> Customers { set; get; }
    }
}

namespace EF_Sample35.Models
{
    public class AlimentaryHabits
    {
        public int Id { get; set; }
        public bool LikesPasta { get; set; }
        public bool LikesPizza { get; set; }
        public int AverageDailyCalories { get; set; }

        public virtual Customer Customer { get; set; }
    }
}

using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Address
    {
        public int Id { set; get; }
        public  string City { set; get; }
        public  string StreetAddress { set; get; }
        public  string PostalCode { set; get; }

        public virtual ICollection<Customer> Customers { set; get; }
    }
}

using System.Data.Entity.ModelConfiguration;
using EF_Sample35.Models;

namespace EF_Sample35.Mappings
{
    public class CustomerAliasConfig : EntityTypeConfiguration<CustomerAlias>
    {
        public CustomerAliasConfig()
        {
            // one-to-many
            this.HasRequired(x => x.Customer)
                .WithMany(x => x.Aliases)
                .WillCascadeOnDelete();
        }
    }
}

using System.Data.Entity.ModelConfiguration;
using EF_Sample35.Models;

namespace EF_Sample35.Mappings
{
    public class CustomerConfig : EntityTypeConfiguration<Customer>
    {
        public CustomerConfig()
        {
            // one-to-one
            this.HasOptional(x => x.AlimentaryHabits)
                .WithRequired(x => x.Customer)
                .WillCascadeOnDelete();

            // many-to-many
            this.HasMany(p => p.Roles)
                .WithMany(t => t.Customers)
                .Map(mc =>
                   {
                       mc.ToTable("RolesJoinCustomers");
                       mc.MapLeftKey("RoleId");
                       mc.MapRightKey("CustomerId");
                   });

            // many-to-one
            this.HasOptional(x => x.Address)
                .WithMany(x => x.Customers)
                .WillCascadeOnDelete();
        }
    }
}

using System.Data.Entity;
using System.Data.Entity.Migrations;
using EF_Sample35.Mappings;
using EF_Sample35.Models;

namespace EF_Sample35.DataLayer
{
    public class Sample35Context : DbContext
    {
        public DbSet<AlimentaryHabits> AlimentaryHabits { set; get; }
        public DbSet<Customer> Customers { set; get; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new CustomerConfig()); 
            modelBuilder.Configurations.Add(new CustomerAliasConfig());
            
            base.OnModelCreating(modelBuilder);
        }
    }

    public class Configuration : DbMigrationsConfiguration<Sample35Context>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(Sample35Context context)
        {
            base.Seed(context);
        }
    }
}

namespace EF_Sample35.Models
{
    public class Customer
    {
        // ...
        public virtual AlimentaryHabits AlimentaryHabits { set; get; }
    }
}

namespace EF_Sample35.Models
{
    public class AlimentaryHabits
    {
        // ...
        public virtual Customer Customer { get; set; }
    }
}

Unable to determine the principal end of an association between 
the types 'EF_Sample35.Models.Customer' and 'EF_Sample35.Models.AlimentaryHabits'. 
The principal end of this association must be explicitly configured using either 
the relationship fluent API or data annotations.

using System.Data.Entity.ModelConfiguration;
using EF_Sample35.Models;

namespace EF_Sample35.Mappings
{
    public class CustomerConfig : EntityTypeConfiguration<Customer>
    {
        public CustomerConfig()
        {
            // one-to-one
            this.HasOptional(x => x.AlimentaryHabits)
                .WithRequired(x => x.Customer)
                .WillCascadeOnDelete();
        }
    }
}

using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Customer
    {
        // ...
        public virtual ICollection<CustomerAlias> Aliases { get; set; }        
    }
}

namespace EF_Sample35.Models
{
    public class CustomerAlias
    {
        // ...
        public virtual Customer Customer { get; set; }
    }
}

using System.Data.Entity.ModelConfiguration;
using EF_Sample35.Models;

namespace EF_Sample35.Mappings
{
    public class CustomerAliasConfig : EntityTypeConfiguration<CustomerAlias>
    {
        public CustomerAliasConfig()
        {
            // one-to-many
            this.HasRequired(x => x.Customer)
                .WithMany(x => x.Aliases)
                .WillCascadeOnDelete();
        }
    }
}

using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Role
    {
        // ...
        public virtual ICollection<Customer> Customers { set; get; }
    }
}

using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Customer
    {
        // ...
        public virtual ICollection<Role> Roles { get; set; }
    }
}

CREATE TABLE [dbo].[RolesJoinCustomers](
 [RoleId] [int] NOT NULL,
 [CustomerId] [int] NOT NULL,
)

using System.Data.Entity.ModelConfiguration;
using EF_Sample35.Models;

namespace EF_Sample35.Mappings
{
    public class CustomerConfig : EntityTypeConfiguration<Customer>
    {
        public CustomerConfig()
        {
            // many-to-many
            this.HasMany(p => p.Roles)
                .WithMany(t => t.Customers)
                .Map(mc =>
                   {
                       mc.ToTable("RolesJoinCustomers");
                       mc.MapLeftKey("RoleId");
                       mc.MapRightKey("CustomerId");
                   });
        }
    }
}

namespace EF_Sample35.Models
{
    public class Address
    {
        public int Id { set; get; }
        public  string City { set; get; }
        public  string StreetAddress { set; get; }
        public  string PostalCode { set; get; }
    }
}

using System.Collections.Generic;

namespace EF_Sample35.Models
{
    public class Customer
    {
       // …
        public virtual Address Address { get; set; }
    }
}

using System.Data.Entity.ModelConfiguration;
using EF_Sample35.Models;

namespace EF_Sample35.Mappings
{
    public class CustomerConfig : EntityTypeConfiguration<Customer>
    {
        public CustomerConfig()
        {
            // many-to-one
            this.HasOptional(x => x.Address)
                .WithMany(x => x.Customers)
                .WillCascadeOnDelete();
        }
    }
}

public class ApiDependencyResolver : IDependencyResolver
    {
        public ApiDependencyResolver(IWindsorContainer container)
        {
            Container = container;
        }

        public IWindsorContainer Container
        {
            get;
            private set;
        }

        public object GetService(Type serviceType)
        {
            try
            {
                return Container.Kernel.HasComponent(serviceType) ? Container.Resolve(serviceType) : null;
            }
            catch (Kernel.ComponentNotFoundException)
            {
                return null;
            }
        }

        public IEnumerable<object> GetServices(Type serviceType)
        {
            try
            {
                return Container.ResolveAll(serviceType).Cast<object>();
            }
            catch (Kernel.ComponentNotFoundException)
            {
                return Enumerable.Empty<object>();
            }
        }
        public IDependencyScope BeginScope()
        {
            return new SharedDependencyResolver(Container);
        }

        public void Dispose()
        {
            Container.Dispose();
        }
    }

    public class SharedDependencyResolver : IDependencyScope
    {
        public SharedDependencyResolver(IWindsorContainer container)
        {
            Container = container;
            Scope = Container.BeginScope();
        }

        public IWindsorContainer Container
        {
            get;
            private set;
        }

        public IDisposable Scope
        {
            get;
            private set;
        }

        public object GetService(Type serviceType)
        {
            try
            {
                return Container.Kernel.HasComponent(serviceType) ? Container.Resolve(serviceType) : null;
            }
            catch (ComponentNotFoundException)
            {
                return null;
            }
        }

        public IEnumerable<object> GetServices(Type serviceType)
        {
            try
            {
                return Container.ResolveAll(serviceType).Cast<object>();
            }
            catch (ComponentNotFoundException)
            {
                return null;
            }
        }

        public void Dispose()
        {
            Scope.Dispose();
        }
    }

public IDependencyScope BeginScope()
{
    var child = container.CreateChildContainer();
    return new ApiDependencyResolver(child);
}

public class KernelInstaller : IWindsorInstaller
    {
        public void Install(IWindsorContainer container, IConfigurationStore store)
        {          
           container.Register(Classes.FromThisAssembly().BasedOn<ApiController>().LifestyleTransient());         
            container.Kernel.Resolver.AddSubResolver(new CollectionResolver(container.Kernel, true));
        }
    }

public class Startup
    {
        public void Configuration( IAppBuilder appBuilder )
        {
           var container = new WindsorContainer();
            container.Install(new KernelInstaller());

            var config = new HttpConfiguration
            {
                 DependencyResolver = new ApiDependencyResolver(container)
            };                  

            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "Default" ,
                routeTemplate: "{controller}/{action}/{name}" ,
                defaults: new { name = RouteParameter.Optional }                
            );

            config.EnsureInitialized();          
         
            appBuilder.UseWebApi( config );
        }
    }

public class Browser
{
    public int Id { get; set; }
    public string Engine { get; set; }
    public string Name { get; set; }
    public string Platform { get; set; }
    public float Version { get; set; }
    public string Grade { get; set; }
}

public class Supplier
{
    [Key]
    public string Key {get; set; }
    public string Name { get; set; }
}
public class Category
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

public class Product
{
    public int ID { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    [ForeignKey("Category")]
    public int CategoryId { get; set; }
    public Category Category { get; set; }

    [ForeignKey("Supplier")]
    public string SupplierId { get; set; }
    public virtual Supplier Supplier { get; set; }
}

public static void Register(HttpConfiguration config)
        {
            ODataModelBuilder odataModelBuilder = new ODataConventionModelBuilder();

            var product = odataModelBuilder.EntitySet<Product>("Products");
            var category = odataModelBuilder.EntitySet<Category>("Categories");
            var supplier = odataModelBuilder.EntitySet<Supplier>("Suppliers");

            var edmModel = odataModelBuilder.GetEdmModel();

            supplier.EntityType.Ignore(c => c.Name);

            config.Select(System.Web.OData.Query.QueryOptionSetting.Allowed);
            config.MaxTop(25);
            config.OrderBy(System.Web.OData.Query.QueryOptionSetting.Allowed);
            config.Count(System.Web.OData.Query.QueryOptionSetting.Allowed);
            config.Expand(System.Web.OData.Query.QueryOptionSetting.Allowed);
            config.Filter(System.Web.OData.Query.QueryOptionSetting.Allowed);
            config.Count(System.Web.OData.Query.QueryOptionSetting.Allowed);

            //config.MapODataServiceRoute("ODataRoute", "odata", edmModel); // کانفیگ به صورت معمولی
           config.MapODataServiceRoute("ODataRoute", "odata",
                builder =>
                {
                    builder.AddService(ServiceLifetime.Singleton, sp => edmModel);
                    builder.AddService<IEnumerable<IODataRoutingConvention>>(ServiceLifetime.Singleton, sp => ODataRoutingConventions.CreateDefault());
                });
}

[EnableQuery]
        public IQueryable<Product> Get()
        {
            return new List<Product>
            {
                new Product { Id = 1, Name = "name 1", Price = 11, Category = new Category {Id =1, Name = "Cat1" } },
                new Product { Id = 2, Name = "name 2", Price = 12, Category = new Category {Id =2, Name = "Cat2" } },
                new Product { Id = 3, Name = "name 3", Price = 13, Category = new Category {Id =3, Name = "Cat3" } },
                new Product { Id = 4, Name = "name 4", Price = 14, Category = new Category {Id =4, Name = "Cat4" } },
            }.AsQueryable(); ;
        }

/odata/Products?$select=Id,Name

/odata/Products?$top=3&$skip=2

/odata/Products?$count=true

{@odata.context: "http://localhost:4516/odata/$metadata#Products", @odata.count: 4,…}
@odata.context:"http://localhost:4516/odata/$metadata#Products"
@odata.count:4
value:[{Id: 1, Name: "name 1", Price: 11, SupplierId: 0, CategoryId: 0},…]
0:{Id: 1, Name: "name 1", Price: 11, SupplierId: 0, CategoryId: 0}
1:{Id: 2, Name: "name 2", Price: 12, SupplierId: 0, CategoryId: 0}
2:{Id: 3, Name: "name 3", Price: 13, SupplierId: 0, CategoryId: 0}
3:{Id: 4, Name: "name 4", Price: 14, SupplierId: 0, CategoryId: 0}

/odata/Products?$filter=Id eq 1

/odata/Products?$filter=Id gt 1 and Id lt 3

/odata/Products?$orderby=Id desc

[EnableQuery(PageSize = 10)]

[EnableQuery(AllowedLogicalOperators=AllowedLogicalOperators.Equal)]

var product = odataModelBuilder.EntitySet<Product>("Products");
product.EntityType.Ignore(e => e.Price);

[EnableQuery(AllowedOrderByProperties = "Id,Name")]

public System.Web.Http.IHttpActionResult GetName(int key)
        {
            Product product = Get().Single(c => c.Id == key);            
            return Ok(product.Name);
        }

HTTP/1.1 200 OK
Content-Type: text/plain; charset=utf-8
DataServiceVersion: 3.0
Content-Length: 3

Ali

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

        [NotFilterable, NotSortable]
        public decimal Price { get; set; }

        [ForeignKey(nameof(SupplierId))]
        [NotNavigable]
        public virtual Supplier Supplier { get; set; }
        public int SupplierId { get; set; }


        [ForeignKey(nameof(CategoryId))]
        public virtual Category Category { get; set; }
        public int CategoryId { get; set; }

        [NotExpandable]
        public virtual ICollection<TestEntity> TestEnities { get; set; }
    }

public DateTimeOffset CreatedOn { get; set; }

/odata/Products?$filter=year(CreatedOn) eq 2016

/odata/Products?$filter=CreatedOn lt cast(2017-04-01T04:11:31%2B08:00,Edm.DateTimeOffset)

/odata/Categories?$expand=Products($filter=Id gt 1 and Id lt 5)

/odata/Categories?$expand=Products($select=Id,Name)

public class MyEnableQueryAttribute : EnableQueryAttribute
{
    public override IQueryable ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
    {
       // Don't apply Skip and Top.
       var ignoreQueryOptions = AllowedQueryOptions.Skip | AllowedQueryOptions.Top;
       return queryOptions.ApplyTo(queryable, ignoreQueryOptions);
    }
}

 public class CaseInsensitiveResolver : ODataUriResolver
    {
        private bool _enableCaseInsensitive;

        public override bool EnableCaseInsensitive
        {
            get { return true; }
            set { _enableCaseInsensitive = value; }
        }
    }

config.MapODataServiceRoute("ODataRoute", "odata",
                builder =>
                {
                    builder.AddService(ServiceLifetime.Singleton, sp => edmModel);
                    builder.AddService<IEnumerable<IODataRoutingConvention>>(ServiceLifetime.Singleton, sp => ODataRoutingConventions.CreateDefault());
                    builder.AddService<ODataUriResolver>(ServiceLifetime.Singleton, sp => new CaseInsensitiveResolver()); // how enable case sensitive
                });

CREATE TABLE Comments (
comment_idSERIAL PRIMARY KEY,
parent_idBIGINT UNSIGNED,
comment TEXT NOT NULL,
FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);

UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;

SELECT parent_id FROM Comments WHERE comment_id = 6; -- returns 4
UPDATE Comments SET parent_id = 4 WHERE parent_id = 6;
DELETE FROM Comments WHERE comment_id = 6;

  WITH CommentTree (comment_id, bug_id, parent_id, author, comment, depth)
AS (
SELECT *, 0 AS depth FROM Comments
WHERE parent_id IS NULL
UNION ALL
SELECT c.*, ct.depth+1 AS depth FROM CommentTreect
JOIN Comments c ON (ct.comment_id = c.parent_id)
)
SELECT * FROM CommentTree WHERE bug_id = 1234;

SELECT * FROM Comments
START WITH comment_id = 9876
CONNECT BY PRIOR parent_id = comment_id;

  CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY,
path VARCHAR(1000),
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)

  SELECT * FROM Comments AS c
WHERE '1/4/6/7/' LIKE c.path || '%' ;

  SELECT * FROM Comments AS c
WHERE c.path LIKE '1/4/' || '%' ;

  CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY,
nsleft INTEGER NOT NULL,
nsright INTEGER NOT NULL,
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

شماره‌ی سمت چپ یک گره از تمام شماره‌های سمت چپ فرزندان آن گره کوچک‌تر و شماره‌ی سمت راست آن گره از تمام شماره‌های سمت راست آن گره بزرگ‌تر است. این شماره‌ها هیچ ارتباطی به comment_id مربوط به آن گره ندارند.

یک راه حل ساده برای تخصیص این شماره‌ها به گره‌ها این است که از سمت چپ یک گره آغاز می‌کنیم و اولین شماره را اختصاص می‌دهیم و به همین به گره‌ای سمت چپ فرزندان می‌آییم و شماره‌ها را به صورت افزایشی به سمت چپ آن‌ها نیز اختصاص می‌دهیم. سپس در ادامه به سمت راست آخرین نود رفته و از آن جا به سمت بالا می‌آییم و به همین ترتیب به صورت بازگشتی تخصیص شماره‌ها را ادامه می‌دهیم.

با اختصتص شماره‌ها به هر گره، می‌توان از آن‌ها برای یافتن نیاکان و فرزندان آن گره بهره جست. برای مثال برای بازیابی گره‌ی 4 و فرزندان (نوه‌های) آن باید دنبال گره‌هایی باشیم که شماره‌های آن گره‌ها بین nsleft و nsright گره‌ی شماره‌4 باشد:

  SELECT c2.* FROM Comments AS c1
JOIN Comments as c2
ON c2.nsleft BETWEEN c1.nsleft AND c1.nsright
WHERE c1.comment_id = 4;

به جای استفاده از جدول Comments برای ذخیره‌ی اطلاعات مربوط به یک درخت از جدول TreePath استفاده می‌کنیم. به ازای هر یک جفت گره در این درخت یک سطر در جدول ذخیره می‌شود که ارتباط پدر فرزندی را نمایش می‌دهد و الزاما نباید این دو پدر فرزند بلافصل باشد. همچنین یک سطر هم به ازای ارتباط هر گره با خودش به جدول اضافه می‌گردد.

پرس‌وجوهای بازیابی نیاکان و فرزندان (گره‌ها) از طریق جدول TreePaths ساده‌تر از روش مجموعه‌های تودرتو است. مثلا برای بازیابی فرزندان (نوه‌های) گره‌ی شماره‌ی 4، سطرهایی که نیاکان آن‌ها 4 است را به دست می‌آوریم:

   SELECT c.*  FROM Comments AS c
JOIN TreePaths AS t ON c.comment_id = t.descendant
WHERE t.ancestor = 4;