How to Join tables with empty fields ?

B M-A 361 Reputation points
2022-10-17T11:58:57.267+00:00

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();  
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,454 questions
0 comments No comments
{count} votes

Accepted answer
  1. Zhanglong Wu-MSFT 261 Reputation points Microsoft Vendor
    2022-10-19T08:25:41.137+00:00

    @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


1 additional answer

Sort by: Most helpful
  1. Jack J Jun 24,306 Reputation points Microsoft Vendor
    2022-10-18T03:29:42.803+00:00

    @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.