0

I have the following Linq to Entity Framework query:

//Get type strings from language depended resource file
string personTypeString = StaticHelpers.GetResourceString("TypeLabel.Person");
string companyTypeString = StaticHelpers.GetResourceString("TypeLabel.Company");

IQueryable<PersonCompany> query = Em.Persons.Select(p => new PersonCompany
                        {
                            DateOfBirth = p.DATE_OF_BIRTH,
                            Name = p.LAST_NAME + " " + p.FIRST_NAME,
                            TaxVatNr = null,
                            CustomerType = personTypeString
                        })
                .Concat( //union all
                    Em.Companies.Select(c => new PersonCompany
                        {
                            DateOfBirth = null,
                            Name = c.NAME,
                            TaxVatNr = c.TAX_VAT_NR,
                            CustomerType = companyTypeString
                        }));

When I try to execute the query I get the following error form the database:

ERROR [42610] [IBM][DB2/LINUXX8664] SQL0418N The statement was not processed because the statement contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value.

The problematic part is Type = personTypeString because in the generated SQL it is replaced with @p__linq__0 as C4.
DB2 expects that the type of this parameter marker to be defined. In SQL you would write it as CAST(@p__linq__0 as VARCHAR(10)) AS C4.

How can I modify my linq query in a way that the generated SQL statement is executed correctly?

Note: The type of query must stay IQueryable<PersonCompany>.

EDIT: Here is the sql query generated by the entity framework:

SELECT 
Project3.C1 AS C1, 
Project3.C5 AS C5, 
Project3.C6 AS C6, 
Project3.C7 AS C7, 
Project3.C8 AS C8
FROM ( SELECT Project3.C1 AS C1, Project3.C5 AS C5, Project3.C6 AS C6, Project3.C7 AS C7, Project3.C8 AS C8, row_number() OVER (ORDER BY Project3.C6 ASC) AS row_number
    FROM ( SELECT 
        UnionAll1.C1 AS C1,
        UnionAll1.DATE_OF_BIRTH AS C5, 
        UnionAll1.C2 AS C6, 
        UnionAll1.C3 AS C7, 
        UnionAll1.C4 AS C8
        FROM  (SELECT 
            CAST(1 AS int) AS C1, 
            Extent1.DATE_OF_BIRTH AS DATE_OF_BIRTH, 
            Concat(Concat(Extent1.LAST_NAME, ' '), CASE WHEN (Extent1.FIRST_NAME IS NULL) THEN '' ELSE Extent1.FIRST_NAME END) AS C2, 
            CAST(NULL AS varchar(45)) AS C3, 
            @p__linq__0 AS C4
            FROM PPMS.PERSON AS Extent1
        UNION ALL
            SELECT 
            CAST(1 AS int) AS C1, 
            CAST(NULL AS timestamp) AS C2, 
            Extent2.NAME AS NAME, 
            Extent2.TAX_VAT_NR AS TAX_VAT_NR, 
            @p__linq__1 AS C3
            FROM PPMS.COMPANY AS Extent2) AS UnionAll1(C1, ID, CRE_DATE, CRE_USER, DATE_OF_BIRTH, C2, C3, C4, UPD_DATE, UPD_USER)
    )  AS Project3
)  AS Project3
WHERE Project3.row_number > 0
ORDER BY Project3.C6 ASC FETCH  FIRST 200 ROWS ONLY 

As I already mentioned, the problem is that there is no type defined for @p__linq__0.

The PersonCompany class is a simple class containing the properties. As @JunaidKirkire requested here is the code for PersonCompany:

public class PersonCompany
{
    private string _name;
    private string _custormerType;
    private DateTime? _dateOfBirth;
    private string _taxVatNr;

    public string Name
    {
        get { return _name; }
        set
        {
            if (_name == value) return;
            _name = value;
            OnPropertyChanged("Name");
        }
    }

    public string CustomerType
    {
        get { return _custormerType; }
        set
        {
            if (_custormerType == value) return;
            _custormerType = value;
            OnPropertyChanged("CustomerType");
        }
    }

    public DateTime? DateOfBirth
    {
        get { return _dateOfBirth; }
        set
        {
            if (_dateOfBirth == value) return;
            _dateOfBirth = value;
            OnPropertyChanged("DateOfBirth");
        }
    }

    public string TaxVatNr
    {
        get { return _taxVatNr; }
        set
        {
            if (_taxVatNr == value) return;
            _taxVatNr = value;
            OnPropertyChanged("TaxVatNr");
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;

    public void OnPropertyChanged(string propertyName)
    {
        if(PropertyChanged != null)
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
    }
}
raznagul
  • 355
  • 2
  • 19
  • `DateOfBirth = null` does not make any sense. If it is nullable type then it is by default null if it is value type than you can not assign null to it. – Jenish Rabadiya Apr 24 '15 at 09:55
  • You are right that I don't need `DateOfBirth = null`. But that is no problem here. And of course it is nullable, a date of birth wouldn't make much sense for companies. – raznagul Apr 24 '15 at 10:02
  • 1
    I think the problem is Type is a keyword – Girish Sakhare Apr 24 '15 at 10:06
  • @raznagul can you please give us the structure of `PersonCompany`? – JunaidKirkire Apr 24 '15 at 10:08
  • @Girish Sakhare: No, Type is no problem here. I changed the name of the property to `CustomerType` just to make sure. But, as expected, the error is the same. – raznagul Apr 24 '15 at 10:15
  • @raznagul have you tried removing both null assignment? `TaxVatNr = null` and `DateOfBirth = null`. – Jenish Rabadiya Apr 24 '15 at 10:20
  • @Jenis Rabadiya: I just tried to remove the null assignments. It turns out that both are needed. If I remove them I get the error the the assignments are not compatible: `The type 'PPMS.Entities.CustomEntities.PersonCompany' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.` – raznagul Apr 24 '15 at 10:24
  • http://stackoverflow.com/questions/13381898/how-to-resolve-sql0418n-error – Girish Sakhare Apr 24 '15 at 10:32
  • @Girish Sakhare: Yes, that is exactly the problem with the sql statement. But as the statement is generated by the entity framework based on my linq query, I can not change it directly. – raznagul Apr 24 '15 at 10:37
  • Can you add a cast in your code? `CustomerType = (string) personTypeString` or `CustomerType = personTypeString as string` or `CustomerType = personTypeString.ToString()`? I'm not familiar with Linq to Entities, just DB2. :) [This question](http://stackoverflow.com/questions/12165805/casting-to-a-derived-type-in-a-linq-to-entities-query-with-table-per-hierarchy-i) seems to indicate the `as` option should work, but I'm not sure if doing this in a `SELECT` part would be different. – bhamby Apr 24 '15 at 13:01

0 Answers0