joining in C# for two condtions

Question

I have to do conditional joining between two tables Price and Operator

Table Prices

CustomerName
UnitPrice
Country
Code

Table Operators

OpertorName
Country
Code

Joining requires to be run on following two conditions:

a. If Code is null for a record in table Prices then join only on basis of Country (all Codes of selected Country will be included from table Operators)

b. If Code is present for a record in table Prices then join on basis of both Country and Code

Please help

//If Code is null in table Price for a record then :

 var reportList = (from pl in Prices
                          join ml in Operators 
                          on pl.Country equals ml.Country                          
                          select new PriceReports {  }

//If Code is present in table Price for a record then 

    var reportList = (from pl in Prices
                          join ml in Operators 
                          on new { a = pl.Country, b = pl.MNC } equals new { a = ml.Country, b = ml.Code }
                          select new PriceReports {  }

I want to create a single query to represent both above conditions

Chrᴉz remembers Monica · Accepted Answer · 2019-08-23 10:23:03Z

3

You cannot write joins like this with the used join syntax in LinQ, you need to use a cross join with where conditions.

var reportList = from pl in PriceS
                 from ml in Operators.Where(mList => mList.Country == pl.Country
                                                   && (pl.Code == null || mList.Code == pl.Code))
                 select new PriceReports {  }

edited Aug 23, 2019 at 10:23

answered Aug 23, 2019 at 8:34

Chrᴉz remembers Monica

1,9201 gold badge12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Next Olive Over a year ago

will this approve both conditions?

Chrᴉz remembers Monica Over a year ago

@NextOlive It joins the table with the condition given in the where-function. In this case the same (CountryRegion.)Name/Country and Code is null OR same Code/MNC.

Next Olive Over a year ago

it is picking only the not null records (the second condition)

Chrᴉz remembers Monica Over a year ago

@NextOlive You were right, I checked for Code== null in the Operators data, you needed it in the prices data. I fixed it and also changed the MNC-Property to Code as described in your table definition (in opposing to MNC in your sample).

Collectives™ on Stack Overflow

joining in C# for two condtions

Table Prices

Table Operators

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table Prices

Table Operators

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related