none
Association with fixed field value RRS feed

  • Question

  • I want to add LINQ entity classes on top of an existing database. We have one table - BasicDropdownText - for basic dropdownlists which has the following definition

    { CodeSelection(PK), TypeSelection(PK), TextSelection}

     

    So we can get the texts of a dropdownlist simply by querying with a certain type. A very typical application of this table would be 'good morning, good afternoon, good evening'. Not good enough for an own table, we are already above 125 types.

     

    Now I also have a table 'Visits'. Each visit has a certain status which is also in BasicDropdownText under type 136. I cannot get LINQ to make such a visit -> BasicDropdownText link. In 'Visit' only the CodeSelection (0, 1 .. 4) is known. Currently we simply join the CodeSelection and add 136 because we know it's 136. 

     

    I can make a class 'Visit' which links to an EntitySet of BasicDropdownText, but it gets all entries with the same code which is more than an overkill. Adding a property 'VisitStatus' wich is readonly and always returns 136 does not work since I cannot use it in the association unless it is a column. When I turn it into a column using the Column attribute of LINQ, an exception is thrown since the column does not exist in the database.

     

    Does anybody know a decent way to work around this? This might otherwise prevent the start of our conversion to LINQ which I consider a very undesired consequence.

    Friday, November 26, 2010 3:58 PM

Answers

All replies

  • Hi,

    Take a look at these links. May be they could be helpful for you:

    http://www.codeproject.com/Articles/94784/Using-Conditional-Mapping-in-Entity-Framework.aspx

    http://www.robbagby.com/entity-framework/entity-framework-modeling-table-per-Hierarchy-inheritance/

    Best regards,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    • Marked as answer by liurong luo Monday, December 6, 2010 10:37 AM
    Saturday, November 27, 2010 9:50 AM
  • JA, thanks for your reply! It seems I get the inheritance working. 

    In the code part below, '100' was chosen as default because I had to pick something. 100 Is not regularly in use, but entries do exist. 

    Imports Microsoft.VisualBasic
    Imports System.Data.Linq
    Imports System.Data.Linq.Mapping
    
    <Table(Name:="TEKSTEN_SELECTIE")> _
    <InheritanceMapping(Code:=136, Type:=GetType(ts_VisitStatus))> _
    <InheritanceMapping(Code:=100, Type:=GetType(ts_Descriptions), IsDefault:=True)> _
    Public MustInherit Class TextSelection
      Protected _CodeSelection As Int16
      Protected _TypeSelection As Int16
      Protected _TextSelection As String
    
    
      <Column(Storage:="_CodeSelection", IsPrimaryKey:=True, DbType:="int NOT NULL", Name:="CODE_SELECTIE", IsDiscriminator:=False)> _
      Public Property CodeSelection() As Int16
        Get
          Return _CodeSelection
        End Get
        Set(ByVal value As Int16)
          _CodeSelection = value
        End Set
      End Property
    
      <Column(Storage:="_TypeSelection", IsPrimaryKey:=True, DbType:="int NOT NULL", Name:="SOORT_SELECTIE", IsDiscriminator:=True)> _
      Public Property TypeSelection() As Int16
        Get
          Return _TypeSelection
        End Get
        Set(ByVal value As Int16)
          _TypeSelection = value
        End Set
      End Property
    
      <Column(Storage:="_TextSelection", IsPrimaryKey:=False, DbType:="nvarchar(50) NULL", Name:="TEKST_SELECTIE", IsDiscriminator:=False)> _
      Public Property TextSelection() As String
        Get
          Return _TextSelection
        End Get
        Set(ByVal value As String)
          _TextSelection = value
        End Set
      End Property
    End Class
    

    The entries with TypeSelection = 136 mark different VisitStatuses. So I also created:

    Public Class ts_VisitStatus
      Inherits TextSelection
    
    End Class
    
    

    I have also created a class CareVisit. Each visit - also new ones - have a certain status.

    Imports Microsoft.VisualBasic
    Imports System.Data.Linq
    Imports System.Data.Linq.Mapping
    Imports System.Linq
    
    <Table(Name:="Care_Visit")> _
    Public Class CareVisit
    
      Private _VisitId As Int16
      Private _CodeEmployee As String
      Private _VisitStatus As Int16
      Private _ts_VisitStatus As EntitySet(Of ts_VisitStatus)
    
      <Column(Storage:="_VisitId", IsPrimaryKey:=True, Name:="VisitId", _
          IsDbGenerated:=True, DbType:="int NOT NULL IDENTITY")> _
      Public Property VisitId() As Int16
        Get
          Return _VisitId
        End Get
        Set(ByVal value As Int16)
          _VisitId = value
        End Set
      End Property
    
      <Column(Storage:="_CodeEmployee", IsPrimaryKey:=False, _
          IsDbGenerated:=False, DbType:="nvarchar(25) NOT NULL")> _
      Public Property CodeEmployee() As String
        Get
          Return _CodeEmployee
        End Get
        Set(ByVal value As String)
          _CodeEmployee = value
        End Set
      End Property
    
      <Column(Storage:="_VisitStatus", CanBeNull:=False, DbType:="int", Name:="VisitStatus")> _
      Public Property VisitStatus() As Int16
        Get
          Return _VisitStatus
        End Get
        Set(ByVal value As Short)
          _VisitStatus = value
        End Set
      End Property
    
      <Association(Name:="ts_VisitStatus", OtherKey:="CodeSelection", ThisKey:="VisitStatus", Storage:="_ts_VisitStatus")> _
      Public ReadOnly Property ts_VisitStatus() As EntitySet(Of ts_VisitStatus)
        Get
          Return _ts_VisitStatus
        End Get
    
      End Property
    End Class
    

    Now the problem I still have is that under ts_VisitStatus, the EntitySet(Of ts_VisitStatus) contains ALL entries from the table TextSelection with that particular CodeSelection. If, however, I change the type of Property ts_VisitStatus to ts_VisitStatus, it remains empty.

    So the inheritance seems to work, but how can I link ONE VisitStatus to ONE CareVisit? 

     

     

    Monday, November 29, 2010 10:43 AM
  • Hi,

    If VisitStatus and CaseVisit has different TypeSelection, you can't.

    But if these both classes inherit form Visit, then you should link ONE VisitStatus or ONE CareVisit to ONE Visit.

    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    Monday, November 29, 2010 2:28 PM
  • The TypeSelection is always 136, that's simply the TypeSelection under which the VisitStatus has been added in TextSelection. The 136 is defined in '<InheritanceMapping(Code:=136, Type:=GetType(ts_VisitStatus))> _'. Still, if I link a CareVisit to ts_VisitStatus, LINQ doesn't see that all ts_VisitStatus must have TypeSelection 136. The 136 is ignored and I get an association to ALL entries which have a CodeSelection which is equal to the column VisitStatus in CareVisit. Any clue about how to fix it will bring me miles further! Just to Clarify: TextSelection 135 - 1 - "Good morning" 135 - 2 - "Good evening" 136 - 1 - "Visit is open" 136 - 2 - "Visit is canceled" 136 - 3 - "Visit is approved" I suppose the InheritanceMapping which links 136 to ts_VisitStatus should do the trick, but an association to ts_VisitStatus still links to ALL entries in TextSelection.
    Monday, November 29, 2010 2:46 PM