none
LINQ OUTER JOINS ON TABLES THAT HAVE MISSING RECORDS AND THEIR PRIMARY KEYS RRS feed

  • Question

  • I'm stumped on this one.  I'm joining a bunch of tables to company.  One of the tables is Person (called contact in the LINQ expression) which has all of its fields defined as being nullable in the SQL Server database, except for the primary key called Pers_PersonID which is defined as a non-nullable integer.

    The LINQ expression that does the join sets the person record to a default when it doesn't exist.  When this happens, the Pers_PersonID field takes on a null value.  The second LINQ query fails on the Pers_Name property which concatenates a bunch of name fields on the person record with the error that a non-nullable int32 field is being set to null.  None of the concatenated fields is an int32 type (they are all strings).  I suspect what is happening is that the Pers_PersonID field is being also brought into scope in the function with a null value.  How do I set what the default shoud be (a zero value) when the person record is empty because of the join?

    The error produced by the second LINQ to SQL query is

    "The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type."

    //add the contacts, addresses, orders, and coordinators for each company
    var objCompanyJoinedTables = (from company in objCompanys
                   join p in DataContext.Persons
                   on company.Comp_PrimaryPersonId equals p.Pers_PersonId
                   into grpPerson
                   from contact in grpPerson.DefaultIfEmpty()
                   join a in DataContext.Addresses
                   on company.Comp_PrimaryAddressId equals a.Addr_AddressId
                   into grpAddress
                   from address in grpAddress.DefaultIfEmpty()
                   join translationOrder in DataContext.TranslationOrders
                   on company.Comp_CompanyId equals translationOrder.CustomerID
                   into grpOrders
                   from order in grpOrders.DefaultIfEmpty()
                   join coordinator in DataContext.EmployeeInfos
                   on order.CoordinatorID equals coordinator.ID
                   into grpEmployees
                   from coordinator in grpEmployees.DefaultIfEmpty()
                   select new {
                         company,
                         contact,
                         address,
                         order,
                         coordinator
                         });
    
    
    //add any co-ordinators, quotes, and orders owned by each person to the filtered resultset
    var objCompanySearchResults = (from record in objCompanyJoinedTables
                    select new {
                          record.company.Comp_CompanyId,
                          record.company.Comp_Name,
                          record.company.Comp_PrimaryPersonId,
                          record.contact.Pers_Name, <==THIS CAUSES ERROR BELOW
                          record.contact.Pers_EmailAddress,
                          record.address.Addr_PrimaryAddress,
                          record.contact.Pers_PhoneNumber,
                          record.coordinator.FullName,
                          record.order.OrderNumber,
                          record.order.QuoteNumber,
                          record.order.InvoiceNumber
                          });
                          
     public string Pers_Name                            
     {                                       
       get                                    
       {                                     
         string firstname = Pers_FirstName == null ? "" : Pers_FirstName.Trim();
         string lastname = Pers_LastName == null ? "" : Pers_LastName.Trim();  
         return firstname + " " + lastname;                   
       }                                     
     }  
    

    Friday, July 29, 2011 9:30 PM

All replies

  • Hi Infotek,

    Welcome!

    >>>"The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type."

    Based on my test, there is null records in objCompanyJoinedTables, so we need to check each record's property to see whether it is null.

    record.company==null?"":record.company.Comp_CompanyId

    >>> record.contact.Pers_Name, <==THIS CAUSES ERROR BELOW

    I think we can contact like this: Pers_Name=record.contact.Pers_FirstName == null ? "" : Pers_FirstName.Trim()+""+record.contact.Pers_LastName == null ? "" : Pers_LastName.Trim();

    If I misunderstood, please feel free to let me know.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 1, 2011 7:46 AM
    Moderator
  • When I add a null check on the problematic line such as

      Pers_Name = record.contact==null ? "" : record.contact.Pers_Name, <==THIS CAUSES ERROR BELOW

    I get back the error

    Could not translate expression 'Table(Company).Where(c => c.Comp_Name.StartsWith...' into SQL and could not treat it as a local expression."} System.SystemException {System.InvalidOperationException}

    This problem doesn't happen on the other contact/person fields

    record.contact.Pers_EmailAddress,
    record.contact.Pers_PhoneNumber

    The problem seems to be that the Pers_Name field is implemented in a property class that ISN'T available when the copntact/person record doesn't exist. 

    My problem isn't to find another way to get the Pers_Name out.  I want to find a general way to handle property classes on null records for any LINQtoSQL query.

     

    Tuesday, August 2, 2011 1:29 PM
  • Actually, I should clarify the problem better.

    We have a SQL Server database which we generated a DBML(schema) and designer.cs(code to access data) files using SQL Metal.  The designer.cs code that was generated has a person/contact class modeled after a person table defined as:

    	[Table(Name="CRM60.dbo.Person")]
    	[DataContract()]
    	public partial class Person : AllLanguagesExtension
    	{
    

     Somebody created ANOTHER class in another file that inherits the person/contact class from designer.cs.  This child class has the definition of:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using LinqCommon; //CreateListType
    using System.Text;
    using ALBusinessLayer.Repository;
    using System.Data;
    
    
    namespace ALBusinessLayer
    {
      public partial class Person
      {
        public string Pers_Name
        {
          get
          {
            string firstname = Pers_FirstName == null ? "" : Pers_FirstName.Trim();
            string lastname = Pers_LastName == null ? "" : Pers_LastName.Trim();
            return firstname + " " + lastname;
          }
        }
    

    We've assumed all LINQ quries call the designer.cs methods to produce their results.  The LINQ join query can access fields in the designer.cs person/contact class without any problem if the joined person record doesn't exist.  But, it can't access the Pers_Name property in the child class on the same "missing record" - a crash occurs.

    Now, if I run a version of the LINQ join query WITHOUT accessing the Pers_Name property, and I change the LastName property on the designer.cs Person class to append a series of "xxxxx" to the lastname, Visual Studio watch shows the lastname field in the retrieved records doesn't have any of the appended x's for either joined missing or present person records.

    And, if I put a breakpoint on the same LastName method, it never trips when the LINQ query is executed - regardless if that query tries to access LastName or Pers_Name in the child class.

    This suggests that LINQ is converting the query into different things depending on what's in the query.  It looks like if I go for LastName, all that's happening is that LINQ generates an SQL statement that goes directly at the database.  The designer.cs classes and DBML file are NOT involved in the execution of the LINQ join.

    If I instead try to select the Pers_Name in the LINQ join, SQL isn't generated.  Instead, something else is created that tries to use the classes in designer.cs.  Because the person record doesn't exist sometimes, these classes may not always be available.  How do I make them available for empty records produced by a LINQ join?

    Any help or pointing me in the right direction would be much appreciated.

    Tuesday, August 2, 2011 3:47 PM
  • Hi,

    Thanks for your feedback.

    The left out join will selete some null records out. Based on your code, I think you are using partial class to add Per_Name to Person class's property,right? You mean you can access "LastName" property without any problems?

    Based on my test, Partial class works ok. you can ping me: v-alache@microsoft.com

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 3, 2011 12:38 PM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance. 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 8, 2011 10:59 AM
    Moderator