• DECEMBER 9, 2022
  • New Delhi, INDIA
Serenity

How to Use Joins in Serenity Using ForeignKey Attribute

  • by Deepak Talwar
  • January 4, 2023
  • 0 Comments
  • 5 minute read
  • 286 Views
  • 0 Likes
How to Use Joins in Serenity Using ForeignKey Attribute

Joins in Serenity Platform

Due to relationships, we frequently do joins when searching databases. These joins tend to be LEFT or INNER joins.
Entities created using Serenity can be used in the same ways as SQL views, allowing you to add fields from other tables and query them as if they were one large merged table.


A. Scenario - if there is some master table like city master as its primary key in User Table

1. Show City Information in User Entity & User Form using LeftJoin Attribute


public class UserRow : Row
{
[Identity, PrimaryKey, [DisplayName("UserId")]
    public Int32? Id
    {
        get { return Fields.Id[this]; }
        set { Fields.Id[this] = value; }
    }

    [DisplayName("City"), ForeignKey("Cities", "Id"), LeftJoin("jc")]
    public Int32? CityId
    {
        get { return Fields.CityId[this]; }
        set { Fields.CityId[this] = value; }
    }

    [Expression("jc.[Name]")]
    public string CityName
    {
        get { return Fields.CityName[this]; }
        set { Fields.CityName[this] = value; }
    }
}

Here
- We are using LEFT JOIN join
- We assign an alias to Cities table as jc when it is connected.
- The join ON expression is jc. With some assistance from the ForeignKey property, jc.[Id] == T0.[CityId].
- CityName is a view field with the expression jc.Name, not a column in the User table. (CityName variable
came from the Name field of the Cities table).


2. Show Country Information in User Entity & User Form Where Country table is directly related with City instead of User


public class UserRow : Row
{
    ....

    ....

    [DisplayName("Country"), Expression("jc.[CountryId]"), ForeignKey("Countries", "Id"), LeftJoin("jct")]
    public Int32? CountryId
    {
        get { return Fields.CountryId[this]; }
        set { Fields.CountryId[this] = value; }    
    }

    [Expression("jct.[Name]")]
    public string CountryName
    {
        get { return Fields.CountryName[this]; }
        set { Fields.CountryName[this] = value; }
    }    
}

Here
- We defined a LEFT JOIN on CountryId field in Cities table.
- We assigned jct alias to Countries table and bring in the name field from it.



B. Scenario - if there User Table has referenced/Primary Key in another table as Foreign Key

Entity classes can additionally have the LeftJoin attribute attached to them. For Example User is Userd in in UserDetail Table and we want to show address from User detail table into User Entity


[LeftJoin("ud", "UserDetails", "ud.[UserId] = T0.[Id]")]
public class UserRow : Row
{
    ....

    ....

    [Identity, PrimaryKey, [DisplayName("UserId")]
    public Int32? Id
    {
        get { return Fields.Id[this]; }
        set { Fields.Id[this] = value; }
    }

    [Expression("ud.[CorrespondenceAddress]")]
    public string CorrespondenceAddress
    {
        get { return Fields.CorrespondenceAddress[this]; }
        set { Fields.CorrespondenceAddress[this] = value; }
    }
}






Deepak Talwar

Deepak Talwar

Technical Architect and Full Stack Developer with 18+ years of Professional Experience in Microsoft Technologies & PHP Platform. Hands on experience with C#, VB, ASP.NET, ASP.NET MVC, ASP.NET Core, ASP.NET Web API, Linq, ADO.NET, Entity Framework, Entity Framework Core, Sql Server, MYSQL, NoSql, Javascript, Angular, jQuery, AWS, Azure, React, Angular, Laravel, Codeingiter, Serenity, VBA, Cloud Computing, Microservices, Design Patterns, Software Architecture, Web Design and Development.

Previous Article

Various Methods of Data Transmission from Controllers to Views | ASP.NET MVC

December 24, 2022
Next Article

Use Expression Attribute in Serenity

January 4, 2023


Related Post

post
Design

Use Expression Attribute in Serenity

January 4, 2023
 

The Most Powerful

.

Stay Connected

Popular

post
Serenity

How to Add Date Editor in Form in Senerity

January 7, 2023
post
Serenity

How to Use Joins in Serenity Using ForeignKey Attribute

January 4, 2023
post
Serenity

How to Build and Customizing Search or Filter on Senerity Listing Page

January 14, 2023
post
Serenity

Use Expression Attribute in Serenity

January 4, 2023
post
Serenity

How to Show Filters Dropdown or LookUp Script on Listing Page in Senerity

January 4, 2023

Hot Categories

Tags

Default (4) Routing (23) Promise (4) Observable (7) Error-Handling (5) Forms (9) LinkedIn (1) Datastream (1) FormControl (1) FormGroup (1) FormArray (1) Validation (1) Linq (2) SEO (1) Visual Studio Code (1) Date (2) XML (1) Operators (2) Data Binding (3) Search String (1) Function (4) Create App (8) Installation (3) Project Structure (1) Introduction (6) Join (2) Expression (3) Filter (3) Link / Button (1) State Hook (1) Cursor (1) Stored Procedure (3) Features (1) Rendering (2) Setup Environment (3) Project Setup (2) Architecture (5) Version History (1) Life Cycle (3) File Folder Structure (6) Controller (1) Comparision (3) Bundling (1) Module (5) Injectors (1) Bootstrap (1) Fundamentals (5) Best Practices (1) Result Type (2) Class Types (2) Override (1) Polymorphism (2) Overloading (1) Method Hide (1) State management (1) Project Templates (1) Razor Pages (1) Model Binding (5) Commands (1) Views (11) Transaction (1) Property (2) Check Null (1) Temporary Table (2) Statement (1) Sequence (1) Trigger (3) Constraint (6) Indexes (10) Normalisation (1) Events Notifications (1) Statistics (1) Data Capture (1) Environment (1) Language Constructs (5) Input/Output (1) File Handling (3) Class & Objects (1) Delegate (1) Exception Handling (2) Event Handling (1) Middleware (3) Hosting Model (3) CLI (2) Tag Helpers (4) HTML Helpers (5) Dependency Injection (3) Data Passing (3) Client-Side Packages (1) Action Result (10) Data Validation (2) JavaScript Extension (1) Constructs (1) ECMAScript (4) Component (2) Styling (2) Hooks (8)

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.

Instagram

gallery1
gallery2
gallery3
gallery4
gallery5
gallery6

Contact Info