Extend Database with new objects

[lc4pro]

Hi,

I'm searching for a way to extend the database in my code. I've a table that is mapped to an object called Book. Now I like to create a inherited class called ScienceBook. This class originates in the same database table and is only different by a db attribute named type.

Can I create a derived object that uses the original class but is only limited by a flag. Can this class use the create and update/delete methods of the original class?

thanks!

greetings!

[StanislavK]

As far as I can understand, you can use TPH inheritance for this. I.e., create all additional columns (one for the flag plus those corresponding to the descendant entity properties) in the table, and then configure the inheritance in your model. See the corresponding article for more information:
http://www.devart.com/linqconnect/docs/Inheritance.html

Please tell us if this helps.

[lc4pro]

Hi,

maybe I didn't understand the tutorial correctly but I found no way to do what I want.

I like to create a inherited class that is a subset of the original EntitySet in the table. Like there is always a .Where(function(x) x.type = SienceBook) class for all operations.

Thanks for help!

[MariiaI]

If we correctly understand you, you are going to get all objects of type "SienceBook" from the base class collection? In this case, it is recommended to use the OfType generic method for this purpose, which allows getting all the objects as required:

Code: Select all

Dim sienceB = dc.BaseClasses.OfType(SienceBook);

Please tell us if this helps.

[lc4pro]

Lets make this a little bit more concrete

here the beginning of my master Book/Producer class generated by EntityDeveloper

Code: Select all

<Table(Name:="producer")> _
    Public Partial Class Producer
        Implements INotifyPropertyChanging, INotifyPropertyChanged

        Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = New PropertyChangingEventArgs(System.String.Empty)

        Private _Id As Integer
        Private _Title As String
        Private _PartnerDisplayStart As System.Nullable(Of System.DateTime) = DateTime.Parse("01/01/0001 00:00:00", System.Globalization.CultureInfo.InvariantCulture)
        Private _PartnerDisplayStop As System.Nullable(Of System.DateTime) = DateTime.Parse("01/01/0001 00:00:00", System.Globalization.CultureInfo.InvariantCulture)

As you can see there are attributes PartnerDisplayStart and PartnerDisplayStop. Now I like to create a derived class from Producer named Partner that is restricted to the two attributes.

If i call DatabaseContext.Partner.Single(x => x.id = 1234) then the Partner Object should be returned according to it's partner status in PartnerDisplayStart and PartnerDisplayStop. If the current Producer is no Partner then nothing should be returned.
Also the update, delete methods should be available.

Thanks for help!

[lc4pro]

As an intermediate step forward to enlightment I thought to extend the partial class generated by EntityDeveloper.

Code: Select all

    Partial Public Class Producer
        Public ReadOnly Property IsPartner As Boolean
            Get
                Return PartnerDisplayStart <= Today And Today <= PartnerDisplayStop
            End Get
        End Property
    End Class

But now I get the following error while executing

Code: Select all

Current = DataContext.Current().Producer.SingleOrDefault(Function(x) x.Id = requestId And x.IsPartner)

System.NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.

Quellfehler:

Zeile 27: Integer.TryParse(Request("id"), requestId)
Zeile 28:
>> Zeile 29: Current = DataContext.Current().Producer.SingleOrDefault(Function(x) x.Id = requestId And x.IsPartner)
Zeile 30: If Current Is Nothing Then
Zeile 31: Response.StatusCode = HttpStatusCode.NotFound

[NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.]
Devart.Data.Linq.Engine.b.a(cx A_0) +12
Devart.Data.Linq.Engine.SqlVisitor.a(SqlNode A_0) +1138
Devart.Data.Linq.Engine.SqlVisitor.c(SqlExpression A_0) +10
Devart.Data.Linq.Engine.b.a(dg A_0) +641
Devart.Data.Linq.Engine.SqlVisitor.a(SqlNode A_0) +74
Devart.Data.Linq.Engine.SqlVisitor.c(SqlExpression A_0) +10
Devart.Data.Linq.Engine.SqlVisitor.f(SqlSelect A_0) +49
Devart.Data.Linq.Engine.b.a(SqlSelect A_0) +114
Devart.Data.Linq.Engine.SqlVisitor.a(bg A_0) +69
Devart.Data.Linq.Engine.b.a(bg A_0) +92
Devart.Data.Linq.Engine.SqlVisitor.a(SqlNode A_0) +1465
Devart.Data.Linq.Engine.SqlVisitor.e(SqlNode A_0) +10
Devart.Data.Linq.Engine.SqlVisitor.f(SqlSelect A_0) +27
Devart.Data.Linq.Engine.b.a(SqlSelect A_0) +114
Devart.Data.Linq.Engine.SqlVisitor.a(bg A_0) +69
Devart.Data.Linq.Engine.b.a(bg A_0) +92
Devart.Data.Linq.Engine.SqlVisitor.a(SqlNode A_0) +1465
Devart.Data.Linq.Engine.i.a(SqlNode A_0, SqlFactory A_1, TypeSystemProvider A_2, dk A_3, e A_4, MetaModel A_5) +93
Devart.Data.Linq.Engine.do.a(SqlNode A_0, al& A_1) +101
Devart.Data.Linq.Engine.do.d(Expression A_0) +166
Devart.Data.Linq.Engine.do.f(Expression A_0) +240
Devart.Data.Linq.DataProvider.c(Expression A_0) +45
Devart.Data.Linq.Table`1.System.Linq.IQueryProvider.Execute(Expression expression) +60
System.Linq.Queryable.SingleOrDefault(IQueryable`1 source, Expression`1 predicate) +287
Usercontrols.Detail.Partner.Page_Load(Object sender, EventArgs e) in Partner.ascx.vb:29
System.Web.UI.Control.OnInit(EventArgs e) +92
System.Web.UI.UserControl.OnInit(EventArgs e) +83
System.Web.UI.Control.InitRecursive(Control namingContainer) +134
System.Web.UI.Control.AddedControl(Control control, Int32 index) +191
System.Web.UI.ControlCollection.Add(Control child) +86
SearchDetail.Page_Load(Object sender, EventArgs e) in SearchDetail.aspx.vb:49
System.Web.UI.Control.OnLoad(EventArgs e) +92
System.Web.UI.Control.LoadRecursive() +54
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

any ideas?

[MariiaI]

If we correctly understood you, the discriminator you are using is computable. LinqConnect doesn't support such discriminators, it supports only simple one-column ones.
Please specify the definition of the database table, which should correspond to your implementation ('Producer' + 'Partner').
As for the NullReferenceException, we have reproduced it. We will investigate it and inform you about the results as soon as possible.

[lc4pro]

this is the corresponding database table

Code: Select all

CREATE TABLE `producer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_tmp` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `sort_title` varchar(255) NOT NULL,
  `description` text,
  `street` varchar(255) DEFAULT '',
  `zip` varchar(45) DEFAULT '',
  `city` varchar(255) DEFAULT '',
  `lockbox` varchar(45) DEFAULT '',
  `lockbox_zip` varchar(45) DEFAULT '',
  `lockbox_city` varchar(255) DEFAULT '',
  `country` varchar(255) DEFAULT '',
  `call_number` varchar(45) DEFAULT '',
  `fax_number` varchar(45) DEFAULT '',
  `url` varchar(255) DEFAULT '',
  `email` varchar(255) DEFAULT '',
  `email_language_english` tinyint(1) DEFAULT '0',
  `email_partner` varchar(255) DEFAULT NULL,
  `highlight` tinyint(1) DEFAULT '0',
  `comment` text,
  `text_formated` longtext,
  `text_plain` longtext,
  `partner_display_start` datetime DEFAULT '0001-01-01 00:00:00',
  `partner_display_stop` datetime DEFAULT '0001-01-01 00:00:00',
  `partner_online` varchar(2) DEFAULT '',
  `partner_print` varchar(10) DEFAULT '',
  `create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `display_start` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `display_stop` datetime DEFAULT '9999-12-31 23:59:59',
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

[MariiaI]

Sorry for the delay. We are sending you a sample project, which demonstrates another way to determine whether the record is 'Partner' or not. You could use additional method which adds condition "PartnerDisplayStart <= Today And Today <= PartnerDisplayStop" to the used LINQ query and executes it on the server.
As for the additional 'IsPartner' property, it cannot be used in server-side LINQ queries, as LinqConnect cannot translate unmapped properties into SQL. (Of course, you can use this property at the client side after the entities are loaded).

[lc4pro]

Hi,

thanks for your testproject but your solution is not as comforting as I hoped so I dug a little deeper and came up with this suprisingly simple code

Code: Select all

    Partial Public Class DataContext
        Public ReadOnly Property Partner As IQueryable(Of Producer)
            Get
                Return Me.GetTable(Of Producer)().Where(Function(x) x.PartnerDisplayStart <= Today And Today <= x.PartnerDisplayStop)
            End Get
        End Property
    End Class

    Partial Public Class Producer
        Public Function IsPartner() As Boolean
            Return CType((PartnerDisplayStart <= Today And Today <= PartnerDisplayStop), Boolean)
        End Function
    End Class

For the moment it works perfectly. Maybe I'm stumbeling over some drawbacks. If so I'll let you know

greetings
moe

[StanislavK]

Thank you for sharing. Yes, this way should be working just as well.

The only possible difference should be the order of conditions in the WHERE clause of the resulting SQL query (the way you described, the 'IsPartner' condition always comes last).