LINQ to SQL Class Syntax RRS feed

  • Question


    Hi everyone!


    Got a big problem. To evaluate the LINQ to SQL technology I have made a database of two tables "tabContact" and "tabContactEMail".


    I added this database to my database connections in VB Express 2008. When I add a new item (LINQ to SQL Class) and drag these two tables in the designer it creates some weird stuff that is definitiely wrong (he makes a tabContact class that is both a collection and has the properties of a single contact itself instead of making one class for a single entity and a collection class). It's simply not useable and throws errors all the time.


    So I though of creating the classes myself. I made a datacontext class that inherits from the standard DataContext class and added two properties (ContactCollection As Table(Of Contact) and ContactEMailCollection As Table(Of ContactEMail)).


    My single entity classes (Contact and ContactEMail) are mapped to the respective database classes and the properties are mapped to the database columsn. Here's a sample for "Contact":


    Code Snippet

    Imports System.Data.Linq

    Imports System.Data.Linq.Mapping

    [Table(Name:="tabContact")] _

    Public Class Contact

    Public myID As Long

    Public myMatchCode As String

    #Region " Eigenschaften "

    #Region " Columns "

    [Column(IsPrimaryKey:=True, IsDbGenerated:=True)] _

    Public Property ID() As Long


    Return Me.myID

    End Get

    Set(ByVal value As Long)

    Me.myID = value

    End Set

    End Property

    [Column()] _

    Public Property MatchCode() As String


    Return Me.myMatchCode

    End Get

    Set(ByVal value As String)

    Me.myMatchCode = value

    End Set

    End Property

    #End Region

    #End Region

    End Class



    Sorry, I had to replace all "correct" brackets with "[" and "]", because otherwise this forum interprets it as an HTML starting tag. In the VB code I have the correct ones.


    When I run the following query it generates an error at the "For Next" Loop saying that is it an invalid cast!


    Code Snippet

    Dim lDatabase As New BusinessDatabase("Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Dokumente und Einstellungen\abcd\Eigene Dateien\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplication1\_db\BusinessDatabase.mdf';Integrated Security=True;User Instance=True")

    Dim lContacts = From eContact In lDatabase.ContactCollection Where eContact.ID = 1 Select eContact

    For Each x In lContacts





    What the hell is going on here?


    Thanks for your advice!


    Friday, May 30, 2008 3:00 PM

All replies

  • First if you have two classes and you drag those two classes to the  linq to sql designer you should have 3 classes created

    one class named tabContact and another class named tabContactEMail. He also creates a class named XXXDataContext

    those are the classes that should be created.

    Inside those 2 classes are created fields and properties one for each field, and are created relations. Relation in one hand is a collection in the other hand is a reference to the parent.

    if you have problems generate with the designer did you try the tool SqlMetal??? is a tool that will also generate this code

    Pedro Rainho
    Saturday, May 31, 2008 7:54 AM
  • Before going further I would really investigate the first problem as much as possible. Do you have a table that refers to itself ? What if you simplify the table ?


    Only if I'm sure it still doesn't work and preferably knowing why I would then workaround the problem... This is to avoid using a complex workaround if a simpler solution exists or at least to know exactly in which case this workaround should be used...


    For now my guess would be that you have a self referenced table or something similar...




    Saturday, May 31, 2008 9:16 AM
  • Can you post here the sql for those tables so we can test?

    Pedro Rainho
    Saturday, May 31, 2008 8:52 PM
  • Hi!


    Thanks for your help I got it to work with SQLMetal.


    Another very important question. I want to develop an object-pool service for a server in a client-server architecture.


    What should that be able to do?


    .) Client uses LINQ to "request" some data from the object-pool

    .) the object pools queries the database with the LINQ-Query and checks which of the result records are not yet loaded to the pool and loads them into the pool collection.

    .) The overall result set (objects that fulfill the query clause and already were in the pool + objects that fulfill the query clause and were not in the pool but in the database) are returned as a strongly-typed list (of contact).


    Problems that I have: Making my "ObjectPool" class ain't that difficult and querying it in LINQ is not the problem: but the ObjectPool gets a LINQ query as a parameter and has to:


    a) Query the Database from it (= a Linq.Table (of xxx) Class)

    b) Query the pool collection from it


    To make this possible I have to adapt the query so that it uses one datacontext in the first and the object collection in the second run (like FROM x IN [collection].Contacts SELECT ........; [collection] has to be the datacontext and then the pool collection).


    The goal is to provide an architecture-wide object pool (for all clients) to reduce database operations and memory (by sharing the same object pool for all clients).


    Is this possible in LINQ? Any suggestions of how to do it? Where to start? Important is that the programmier using the object pool class doesn't have to think about where the data actually came from (like: was it in the pool? came it from the database?) he just queries the object pool and the pool does the rest.




    Monday, June 2, 2008 9:51 AM