Use NHibernate / Entity Framework instead of YesSql.

[RockNHawk]

YesSql currently doesn't support complex expression query, and have bugs sebastienros/yessql#262

The Document & Index table design is a cool idea, but sometime it's unnecessary, NHibernate, Entity Framework are relatively more mature, reliable, and powerful.

So we can integrate other ORMs in Orchard Core by ourselves, bypass YesSql, and even break away from Orchard Core's database structure (Document & Index table design)

And, add some Mapping for ContentItemIndex or other XxxxxIndex , it is also feasible to use NHibernate to do the ContentItem + Index query.

I have done this in actual projects and it works well.

[hishamco]

I have done this in actual projects and it works well.

AFAIK OC are set on top of YesSQL, so how you achieve what you did?

[Skrypt]

This is just an opinion but I think YesSQL is faster than EF or NHibernate. It uses Dapper in the backend which is appropriate for this kind of project. Dapper is used and maintained by StackOverflow website guys which is a real production heavy website.

Of course nothing prevents you from using EF or NHibernate if you prefer to do so. YesSQL has ongoing development just like EF an NHibernate has. We faced issues with NHibernate in 01 too and I personally prefer YesSQL/Dapper. An example of using EF can be found in the OrchardCore.OpenId module which allows you to use YesSQL or OpenIddict.EntityFrameworkCore as it's ORM.

[kevinsmgov]

@hishamco, I have a similar use case (CMS coexisting with complex RDBMS system where Entity Framework would drastically simplify access). Do you have example source you could share? e.g. setting up EF migration to use same DB connection string as Yessql, etc...

[ns8482e]

@kevinsmgov CMS is tied to YesSQL ( ContentItem/Indexes/Migration etc.), However complex RDBMS system with your own schema is pluggable with choice of your tech stack with Orchard. You can use IConfiguration / IShellConfiguration to bind to connection string and initiate EF/NHibernate connections for other RDBMS data.

[kevinsmgov]

That's encouraging. Any example code you are aware of would be helpful (I haven't found any googling).

[ns8482e]

I guess it's same as in ASP.NET Core - However define in Module's startup/Tenant pipeline

       services.AddDbContext<MyDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

If you need tenant info you can inject ShellSettings settings to get tenant info - i.e. settings["ConnectionString"] gives you connection string of SQL DB for tenant