HasMany mapping on child tables on loose foreign key with different data type

42 Views Asked by At

I have this issue on Nhibernate where I'm trying to map

Documents to Student

class Student 
{
   public virtual int StudentId { get; set; }

   public virtual ICollection<Document> Documents { get; set; }
}

class Document 
{
   public virtual int DocumentId { get; set; }
   public virtual string EntityId { get; set; }
   public virtual int EntityTypeId { get; set; }
}

class StudentMap 
{
    public StudentMap()
    {
         Schema("dbo");
         Table("Student");
         Id(x => x.StudentId);

         HasMany(x => x.Documents)
              .KeyColumns.Add("EntityId", x => x.SqlType("int"))
              .Where(String.Format("EntityTypeId = {0}", (int)EntityType.STUDENTDOC))
              .Cascade
              .AllDeleteOrphan();
    }
}

The problem is that the generated query set the studentid parameter as integer but the EntityId has different datatype like guid and int.

exec sp_executesql N'SELECT documents0_.EntityId as entityid2_185_1_, documents0_.DocumentId as documentid1_185_1_, documents0_.DocumentId as documentid1_185_0_, documents0_.EntityId as entityid2_185_0_, documents0_.Description as description3_185_0_, documents0_.Metadata as metadata4_185_0_, documents0_.DocType as doctype5_185_0_, documents0_.OwnerEntityId as ownerentityid6_185_0_, documents0_.DocumentName as documentname7_185_0_, documents0_.SharedWithStudent as sharedwithstudent8_185_0_, documents0_.SharedWithAgencyPersonnelDate as sharedwithagencypersonneldate9_185_0_, documents0_.UploadDate as uploaddate10_185_0_, documents0_.RepositoryPath as repositorypath11_185_0_, documents0_.RepositoryDocumentIdentifer as repositorydocumentidentifer12_185_0_, documents0_.RepositoryFolder as repositoryfolder13_185_0_, documents0_.RepositoryRegion as repositoryregion14_185_0_, documents0_.RepositoryProvider as repositoryprovider15_185_0_, documents0_.EntityTypeId as entitytypeid16_185_0_, documents0_.OwnerEntityTypeId as ownerentitytypeid17_185_0_ FROM dbo.Document documents0_ WHERE  (documents0_.EntityTypeId = 85) and documents0_.EntityId=@p0',**N'@p0 int'**,@p0=71339

And because of this, it throws this error:

Conversion failed when converting the varchar value '829C3776-8262-EE11-87A0-F44EFC1F8C0D' to data type int.

Just wondering how can I force the student id to check using nvarchar instead of int.

Change @p0 int to @p0 nvarchar(36).

Or what would be the best approach to handle this?

Note that I can't change the way how tables were setup because it's a legacy database and I just need to map the tables into entities using NHibernate

0

There are 0 best solutions below