Support Entity Framework

[aaronamm]

Hello, everyone.

When we want to add a new feature to Orchard website and make it as a new custom module. Sometime, it is valid to use a traditional relation database system and use Entity Framework as ORM for data access layer.

I am not sure if you like the idea to add a new custom module e.g. OrchardCore.EF to make Orchard supports EF and provide DbContext to other modules or not.

Here is the PoC project that I made. https://github.com/codesanook/OrchardCore.EF

If you agree with the idea, I am happy to make a PR for OrchardCore.EF and add other missing items:

• Auto commit at the end of web request
• Auto rollback when there is an exception
• README and how to consume DbContext in other module projects.

Thanks.

[hishamco]

OC is heavily using YesSQL, but this didn't prevent to explore what you did

Thanks

[hishamco]

After a quick look seems what you did is OrchardDbContext which is a good start, but as I said before OC is heavily using YesSQL, so I will keep this open to hear from others, but if you want to make some progress we can add this to OCC after propose a good design

Feel free to open issue in OrchardCoreContrib if you are interested, because this a good thing for the community even OC doesn't support it officially

Thanks again

[aaronamm]

@hishamco Thank you for your suggestion. I will open an issue to OrchardCoreContrib.

I hope to hear other people opinion as well. Thanks for keeping this PR open.

[hishamco]

Also this a very good clarification from @jtkech

[aaronamm]

@hishamco
FYI, here the code that consumes OrchardCore.EF module. https://github.com/codesanook/Codesanook.ThailandAdministrativeDivisionTool/blob/develop/Controllers/HomeController.cs

Service filter to auto commit a transaction. https://github.com/codesanook/OrchardCore.EF/blob/develop/Filters/TransactionActionServiceFilter.cs

A database migration https://github.com/codesanook/Codesanook.ThailandAdministrativeDivisionTool/blob/develop/Migrations.cs

Mapping class https://github.com/codesanook/Codesanook.ThailandAdministrativeDivisionTool/blob/develop/Mappings/ProvinceConfiguration.cs

[hishamco]

I understand what you refer to, but if you look closely to the link I sent before, you will realize that ContentTypes, Contents, ContentManagement and others are rely on YesSQL, that is why I suggested to open an issue in OCC to see if we can abstract this

[aaronamm]

@hishamco Okay, I will open issue at OrchardCore Contrib project. Thanks for prompt reply.

[aaronamm]

@hishamco I have already opened an issue to OrchardCoreContrib. Here is the link: https://github.com/OrchardCoreContrib/OrchardCoreContrib.Modules/issues/70

[aaronamm]

@hishamco Here is a query that I need to issue both EF query and YesSql. https://github.com/codesanook/Codesanook.ThailandAdministrativeDivisionTool/blob/develop/Controllers/HomeController.cs#L30 I can't make only one database round trip with SQL join. Therefore, I need to make two database round trip and make LINQ query in a memory.

[hishamco]

I think you can do similar thing with YesSQL querying APIs

[aaronamm]

@hishamco Could you please give me an idea how to join query with YesSql? I could not find any example to join two tables. The closest query that I found is only join index but run the document not a new join result set.

I know can use dapper and join over index of UserTable and my Province table. I think we already have EF in this custom module so we should use it instead of using Dapper.

[hishamco]

Have a look to https://github.com/sebastienros/yessql/blob/b8c4c7d554c2944831ce1e8f962b6e180e5cfcc5/test/YesSql.Tests/CoreTests.cs#L1468

[aaronamm]

@hishamco I will try but I am not sure it will work on my query.

In code example.

    Assert.Equal(2, await session.Query().For<Article>().With<PublishedArticle>().With<ArticlesByDay>(x => x.DayOfYear == new DateTime(2011, 11, 1).DayOfYear).CountAsync());

We join two index of articles which are PublishedArticle and 1ArticlesByDayand then getArticle` model with is a main document. It is index join index => main document

My is 'Index table of User' join 'Province table' => new join result set that container Username + Fields from Province table.

I don't know if YesSql work for this scenario. I am not sure we can project the return result to a new object type instead of document model.

I think session.Query can return only index and document model. Please correct me if I am wrong.

Another approach that I can think of is:

• Make a column coverage index on UserTable
• Map UserIndex to EF
• Join User index and Province with LINQ to EF.
• Which I think it is not right way.

[sebastienros]

I think only the CMS related features should assume that YesSql is used or can be used. All other features (user management, role management, open id, admin dashboard, theming, deployment, media, ...) should be decoupled from yessql, with the notion of Store (like Identity stores) such that we could use EF or other orms for these features.

We can use YesSql by default but we need to be able to switch with other implementations in case a system requires it. You might have to use EF, or might need to store in blog storage ...

[hyzx86]

Hi @aaronamm , I have a similar idea https://github.com/OrchardCMS/OrchardCore/discussions/9911

[hyzx86]

I have a better solution, using FreeSql

https://github.com/EasyOC/EasyOC/blob/3477ef6a2d0bf59c8d0d446e0d15acff40fd5db3/src/Modules/EasyOC.OrchardCore.OpenApi/Services/Users/UsersAppService.cs#L74

         var users = FreeSqlSession.Select<UserIndex, UserProfileIndex>()
                 .LeftJoin((ui, up) => ui.DocumentId == up.DocumentId)
                 .WhereIf(input.DepartmentId.IsNullOrWhiteSpace(), (ui, up) => up.Department == input.DepartmentId)
                 ;
            if (!string.IsNullOrWhiteSpace(input.Filter))
            {
                var normalizedSearchUserName = _userManager.NormalizeName(input.Filter);
                var normalizedSearchEMail = _userManager.NormalizeEmail(input.Filter);
                users = users.Where((ui, up) =>
                    ui.NormalizedUserName.Contains(normalizedSearchUserName) ||
                    ui.NormalizedEmail.Contains(normalizedSearchEMail));
            }

But since the Document property of MapIndex is set to private, so when you need to query something like ContentItemIndex you need to create another table that tells FreeSql how to map DocumentId

Fortunately, there are not too many tables, and we can generate them using commands https://github.com/EasyOC/EasyOC/blob/3477ef6a2d0bf59c8d0d446e0d15acff40fd5db3/src/Modules/EasyOC.OrchardCore.RDBMS/OrchardCoreIndexModels/ContentItemIndex.cs