How to Join tables with empty fields ?

Question

Hello,

I want to query database and return 'Customer' Accouunt with bank account and 'Customer' Account without Bank account (empty field; each customer have two accounts), but I receive this error :
System.ServiceModel.FaultException`1: 'The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

 Customer_Identification       Region               Customer Account  
  
 | Id                          |Region_Id           |Customer Id  
 | Name                        |Region_Name         |Bank_Name  
 | Address                                          |Bank Account  
 | Region_Id  
 | Bank Account  

var query = (from select in data.customer_identification  
                         join select1 in data.region on select.Id equals select1.Region_I  
                         from select2 in data.customer   
                         join select3 in data.Customer_Account on select2.Bank Account equals select.Bank Account into ContractJoin  
                         from select4 in ContractJoin.DefaultIfEmpty()  
                         where sel.Id == Id  
  
                         select new Report_C  
                         {  
                             Denumire = sel.denumire,  
                             ID = select.Id  
                             Name = select.Name,  
                             Adress = select.Address,  
                             Region = select1.Region_Name,  
                             Bank = select4.Bank_Name  
      Account = select4.BankAccount  
  
                         }).ToList();  

Answer 1

@B M-A ,

Since Bank_Account can be null, please change the Back_Account to Nullable<int>, like below.

public class Report_C  
{  
            public int Id { get; set; }  
            public string Name { get; set; }  
            public string Address { get; set; }  
            public int Region_Id { get; set; }  
            public Nullable<int> Bank_Account { get; set; }   
            public string Region_Name { get; set; }   
            public int Customer_Id { get; set; }       
            public string Bank_Name { get; set; }  
                 
}  

And change your query code like below:

public IEnumerable<Report_C>GetReportC(int id)  
 {  
 var query = (from select in data.customer_identification  
 join select1 in data.region on select.Id equals select1.Region_I  
 from select2 in data.customer  
 join select3 in data.Customer_Account on select2.Bank Account equals select.Bank Account into ContractJoin  
 from select4 in ContractJoin.DefaultIfEmpty()  
 where sel.Id == Id  
 select new Report_C  
 {  
 Denumire = sel.denumire,  
 ID = select.Id  
 Name = select.Name,  
 Adress = select.Address,  
 Region = select1.Region_Name,  
 Bank = select4.Bank_Name  
 Account = select4.BankAccount ?? 0  
 }).ToList();  
 }  

Best regards,
Zhanglong

Answer 2

@B M-A , Welcome to Microsoft Q&A, based on my test, I find that your code has some problems, so I make some changes.

To facilitate testing, I used list to test the related data.

Here is a code example you could refer to.

 internal class Program  
     
    public class Customer_Identification  
    {  
        public int Id { get; set; }  
  
        public string Name { get; set; }  
  
        public string Address { get; set; }  
  
        public int Region_Id { get; set; }  
  
        public int Bank_Account { get; set; }  
    }  
  
    public class Region  
    {  
  
        public int Region_Id { get; set; }    
  
        public string Region_Name { get; set; }  
    }  
  
    public class Customer_Account  
    {  
        public int Customer_Id { get; set; }  
  
        public string Bank_Name { get; set; }  
  
        public int Bank_Account { get; set; }  
    }  

The error means that you could not convert int type to nullable type because int is value type instead of reference type.

BTW, I did not see the related code about setting the empty field. Could you provide it?

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.