LINQ: Designing DB relations

[K Viltersten]

I've got me a database and after i've D&D'ed
two tables onto DBML i discovered that i could
define a relation between them, making the one
a parent and the other a child.

I noticed that the primary key in the parent
is a column in the child. I guess it makes it
a foreign key in the other table. But what
does it mean for my C# code when i'm using the
mapped instances of my classes?

 

[Jon Skeet [C# MVP]]

I've got me a database and after i've D&D'ed
two tables onto DBML i discovered that i could
define a relation between them, making the one
a parent and the other a child.

I noticed that the primary key in the parent
is a column in the child. I guess it makes it
a foreign key in the other table. But what
does it mean for my C# code when i'm using the
mapped instances of my classes?

The easiest thing is to look in the designer-generated code - it's only
C#

But usually (in LINQ to SQL anyway) it will just be a reference to the
instance of the parent from the child, and potentially a collection of
children from the parent.

 

[K Viltersten]

I've got me a database and after i've D&D'ed

The easiest thing is to look in the
designer-generated code - it's only C#
But usually (in LINQ to SQL anyway) it will just
be a reference to the instance of the parent from
the child, and potentially a collection of
children from the parent.

I believe i managed to be unclear regarding my
question. Sorry.

Suppose we have mapped two classes, C1 and C2. The
C1 class corresponds to table with columns alpha,
which is a PK. C2 class corresponds to columns beta
(PK) and gamma.

If we now set up an association between C1 and C2,
what issue does it resolve for us? I can't see any
advantage coming from the extra step... (Not
claiming for that matter that there isn't any.)

 

[Jon Skeet [C# MVP]]

I believe i managed to be unclear regarding my
question. Sorry.

Suppose we have mapped two classes, C1 and C2. The
C1 class corresponds to table with columns alpha,
which is a PK. C2 class corresponds to columns beta
(PK) and gamma.

If we now set up an association between C1 and C2,
what issue does it resolve for us? I can't see any
advantage coming from the extra step... (Not
claiming for that matter that there isn't any.)

It allows you to navigate from an instance of C1 to a related instance
of C2. For example, suppose C1 is "Person" and C2 is "Address" you
would be able to do:

Console.WriteLine (myPerson.HomeAddress.Road);

 

[K Viltersten]

Suppose we have mapped two classes, C1 and C2. The

It allows you to navigate from an instance of C1 to
a related instance of C2. For example, suppose C1 is
"Person" and C2 is "Address" you would be able to do:

Console.WriteLine (myPerson.HomeAddress.Road);

Ah, _that_ nice and easy? Great!
Thanks for the info!

 

[Frans Bouma [C# MVP]]

Ah, that nice and easy? Great!
Thanks for the info!

Realize that if 'HomeAddress' isn't loaded, it will be loaded from the
db. If you go overboard with this, you'll quickly hurt performance
pretty badly. Consider a grid to which you bind a list of orders and in
one column you bind Customer.CompanyName, to show the related
customer's companyname. That's 1 query per row in the grid

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Marc Gravell]

Realize that if 'HomeAddress' isn't loaded, it will be loaded from the db.
Note also that in the pending Entity Framework you can preload the
relations using some settings on the context (to avoid this "1 query
per row" issue).

Another (better, IMO) solution is to use a specific projection for the
grid; if you navigate the relation *in the projection*, then this step
is done as part of the main query, i.e.

var query = from person in ctx.People
select new
{
person.Forename,
person.Surname,
person.Address.Line1,
person.Address.Postcode
};
dataGridView1.DataSource = query.ToList();

This is then highly optimal; one round-trip, and no unnecessary data
fetched.

Marc

 

[Andrus]

Marc,

Another (better, IMO) solution is to use a specific projection for the
grid; if you navigate the relation *in the projection*, then this step
is done as part of the main query, i.e.

var query = from person in ctx.People
select new
{
person.Forename,
person.Surname,
person.Address.Line1,
person.Address.Postcode
};
dataGridView1.DataSource = query.ToList();

This is then highly optimal; one round-trip, and no unnecessary data
fetched.

I want to allow user to change Forename and Surname in grid.
How to write those changes back to database to Persons table?

Andrus.

 

[Marc Gravell]

Anon types are immutable; LINQ only respects changes to regular entities -
so you would use the pre-load apprach (not the projection approach).

If you are having difficulty getting the properties of sub-objects to
disply, then you can fix that by using a facade around the regular LINQ
entity to flatten the perceived structure - i.e. something like

class Foo {
private readonly bar;
public Foo(Bar bar) {this.bar = bar;}
public string Forename {
get {return bar.Forename;}
set {bar.Forename = value;}
}
// ...
public string Postcode {
get {return bar.HomeAddress.Postcode;}
set {bar.HomeAddress.Postcode = value;}
}
}

and in your projection:
from bar in ctx.Bars
//...
select new Foo(bar);

Because the LINQ EF context created the "bar", it should still be
change-tracked by the context, so apply-changes should work fine. The
pre-load prevents the first "bar.HomeAddress" from doing a round-trip.

Marc

 

[Andrus]

Marc,

Because the LINQ EF context created the "bar", it should still be
change-tracked by the context, so apply-changes should work fine. The
pre-load prevents the first "bar.HomeAddress" from doing a round-trip.

There may be case where only primary key properties at design time.
All other properties can specified by user at runtime.

Which is best solution in this case:

1. Create new regular entity for every update. Copy anon properties to this
regular entity, Attach( entity, true) it and SubmitChanges().
2. Use your solution and create class Foo using dynamic compiling.
3. Maybe is it possible to make anon type updatable in some way ?

?

Andrus.

 

[Marc Gravell]

1 seems a corruption of the object model; 3 is not possible
that leaves 2, but I think you have given me a false trichotomy...

how about:
4: don't use dynamic compiling [at all], and use a standard compiled facade
when a facade is warranted

I admit it doesn't fit your chosen design, but you asked me which option I
thought was best...

Marc

 

[Andrus]

Marc,

4: don't use dynamic compiling [at all], and use a standard compiled
facade when a facade is warranted

Thank you.

I'm sorry I don't understand this solution.
User wants to select linq entity type properties to edit in Winforms
DataGridView at runtime.

Should we create new application and compile desired entity type for every
user every time when she/he desides to change
the properties required to edit ?

Andrus.

 

[Marc Gravell]

No; just have a single facade that exposes pretty-much everything that the
user can choose from, and simply decide (at runtime) which (bound) columns
to add to the DataGridView.

Marc

 

[Andrus]

Marc,

No; just have a single facade that exposes pretty-much everything that the
user can choose from, and simply decide (at runtime) which (bound) columns
to add to the DataGridView.

I'm currently using this appoach.
In this case Linq generated SELECT statement retrieves always all columns
from server over internet connection.
This is unnessecarily slow. Usuall only 5-30% of columns are edited in
single grid. So I'm looking for a way to retrieve
only reqiured columns from database to improve perfomance.

Andrus.