Get Data from more than 1 table

Nov 2, 2010 at 9:14 AM

Hello,

One of my friend suggested me this Orm for our Windows Mobile Applications.I want to discuss it with you as suppose i have two tables- Customers And Orders.

Customers - CustomerID(Primary Key), CustomerName

Orders - OrderId, CustomerID(ForeignKey), OrderDate

Now we have designed two entities as - >

[Entity(KeyScheme.Identity)]
    public class Customers
    {
        [Field(IsPrimaryKey = true)]
        public int CustomerID { get; set; }

        [Reference(typeof(Orders), "CustomerID", Autofill=true)]
        public Orders[] Orders { get; set; }

        [Field(SearchOrder = FieldSearchOrder.Ascending)]
        public string CustomerName { get; set; }
    }

 

[Entity(KeyScheme.Identity)]
    public class Orders
    {
        [Field(IsPrimaryKey=true)]
        public int OrderID { get; set; }

        [Field]
        public int CustomerID { get; set; }

        [Field]
        public string Date { get; set; }

    }

 

Now i want to get this Columns  in my result- >

  OrderID     CustomerName        Date

 

In which manner we can do this?

Coordinator
Nov 2, 2010 at 2:06 PM

You're thinking about this wrong.  You don't "get columns" with an ORM.  You retrieve a set of entities. So first you have to decide what data you want.  For example, you may have this:

"I want all orders for customer ID 23"

In which case you'd do:

var customer = Store.Select<Customers>(23).FirstOrDefault(); // this gets the customer ID 23 and fills their Orders array

or maybe "I want all orders for all customers between 'start' and 'end'"

var customers = Store.Select<Customer>().Where(c => c.Date > start && c.Date < end);

Nov 2, 2010 at 2:25 PM

But in my case Orders and Customers are different-2 Entitites.

var customer = Store.Select<Customers>(23).FirstOrDefault();

This will give me all the Data from the Customer Entity.am i right? I think it will not give me the Orders for that particular Customer.

Coordinator
Nov 2, 2010 at 2:39 PM

Yes, it should fill the Orders.  Look at line 990 of the SqlCeDataStore - if the entity has References, it calls FillReferences to populate the reference array.