OrmLite, SQL Server and loading data for multiple related classes in a single select

[mrboring]

Hi All

 

I am new to ServiceStack.net and an average (at best) programmer. I'm investigating OrmLite against SQL Server (hoping to replace EF with it). I'm using a legacy database (I can't alter the structure of this database and so can't use text blobs).

 

I have two tables:

 

CREATE TABLE [Suppliers].[Contact] (

    [Id]                INT            IDENTITY (1, 1) NOT NULL,

    [Name]              NVARCHAR (100) NOT NULL,

    [Telephone]         NVARCHAR (50)  NULL,

    [Email]             NVARCHAR (100) NOT NULL,

    [PreferenceRanking] INT            NULL,

    [Notes]             NTEXT          NULL,

    [VendorId]          INT            NOT NULL,

    [TimeStamp]         ROWVERSION     NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [FK_Contact_Vendor] FOREIGN KEY ([VendorId]) REFERENCES [Suppliers].[Vendor] ([Id])

);

 

CREATE TABLE [Suppliers].[Vendor] (

    [Id]            INT            IDENTITY (1, 1) NOT NULL,

    [AccountNumber] NVARCHAR (50)  NOT NULL,

    [CompanyName]   NVARCHAR (100) NOT NULL,

    [Notes]         NTEXT          NULL,

    [TimeStamp]     ROWVERSION     NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)

);

It's a one to many relationship.  A Vendor can have multiple Contacts.

I've created the following POCO's (which may need updating to accommodate what I'm trying to do):

 

    [Schema( "Suppliers" )]

    public class Contact

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string Telephone { get; set; }

        public string Email { get; set; }

        public int PreferenceRanking { get; set; }

        public string Notes { get; set; }

        public byte[] TimeStamp { get; set; }

        public int VendorId { get; set; }

    }

 

    [Schema( "Suppliers" )]

    public class Vendor

    {

        public int Id { get; set; }

        public string AccountNumber { get; set; }

        public string CompanyName { get; set; }

        public string Notes { get; set; }

        public byte[] TimeStamp { get; set; }

    }

 

Can I hydrate a collection of Contacts with each Contact having a property that contains the Vendor?  Can this be done with a single query?:

 

SELECT

*

FROM

Suppliers.Contact c

INNER JOIN Suppliers.Vendor v ON c.VendorId = v.Id

 

I can do this easily in EF, but can't figure out how to do it in OrmLite. This is what the POCO's look like for EF:

 

    public class Contact

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string Telephone { get; set; }

        public string Email { get; set; }

        public Nullable<int> PreferenceRanking { get; set; }

        public string Notes { get; set; }

        public int VendorId { get; set; }

        public byte[] TimeStamp { get; set; }

   

        public virtual Vendor Vendor { get; set; }

    }

 

 

    public class Vendor

    {

        public Vendor()

        {

            this.Contacts = new HashSet<Contact>();

        }

   

        public int Id { get; set; }

        public string AccountNumber { get; set; }

        public string CompanyName { get; set; }

        public string Notes { get; set; }

        public byte[] TimeStamp { get; set; }

   

        public virtual ICollection<Contact> Contacts { get; set; }

    }

Thanks in advance for any assistance.

Alan

 

 

 

[Demis Bellot]

Can I hydrate a collection of Contacts with each Contact having a property that contains the Vendor?  Can this be done with a single query?:

 

Nope, not in 1 query with those POCOs. If you want it back in 1 query you would have to create a hybrid table that contains fields from both tables, see:

http://stackoverflow.com/questions/8616516/servicestack-ormlite-how-to-include-field-from-foreign-key-lookup

Otherwise you'd have to do 2 queries.

Cheers,

--
- Demis

http://twitter.com/demisbellot

http://www.servicestack.net/mythz_blog

[mrboring]

Hi Demis

Thanks for the quick response.

Alan