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));
}
}