Entity Splitting question

N

Nick

Hello,

I've read through some articles and forums, but I'm still not sure what the
best solution is. I've got two tables, let's say TableA and TableB. It's a
one to many relationship from A to B. I want to do a join and display the
results in a datagridview. When I do the join I'll be limiting the results
based on a foreign key in TableB, so that it ends up being one to one. I hope
I'm making sense. I want to display the results of the join (both tables) in
the grid. How can I do this?

The keys are not the same, so I can't use entity splitting, right? I don't
want to allow the user to edit TableA, just TableB.

Here is some more info. The main table in my database is "Document" and
there is another table "GlobalVariables" (TableA). A document can write over
the global variable with a record in the "DocumentVariable" table (TableB).
So the DocumentVariable table has a GlobalVariableID and DocumentID. For a
given document I want to show the DocumentVariable records joined with the
GlobalVariable record so the user can see the global variable value and at
the same time show the value they are using. The global value in this screen
is not editable.

I sincerely appreciate any help.

Thanks,
Nick
 
M

Mr. Arnold

Nick said:
Hello,

I've read through some articles and forums, but I'm still not sure what
the
best solution is. I've got two tables, let's say TableA and TableB. It's a
one to many relationship from A to B. I want to do a join and display the
results in a datagridview. When I do the join I'll be limiting the results
based on a foreign key in TableB, so that it ends up being one to one. I
hope
I'm making sense. I want to display the results of the join (both tables)
in
the grid. How can I do this?

The keys are not the same, so I can't use entity splitting, right? I don't
want to allow the user to edit TableA, just TableB.

Here is some more info. The main table in my database is "Document" and
there is another table "GlobalVariables" (TableA). A document can write
over
the global variable with a record in the "DocumentVariable" table
(TableB).
So the DocumentVariable table has a GlobalVariableID and DocumentID. For a
given document I want to show the DocumentVariable records joined with the
GlobalVariable record so the user can see the global variable value and at
the same time show the value they are using. The global value in this
screen
is not editable.

I sincerely appreciate any help.

Why can't you use a "Shaped Linq Query" that allows you to shape the results
of the query only with the fields you want from the query.

Or if you know how to do what you want with T-SQL in a sproc or inline code
as an example and you are using the ADO.NET Entity Framework, then you can
use Entity SQL or ESQL like you can use T-SQL, with using a Database reader.

That way, you can make the ESQL statement using the two tables, select the
fields you want, use a Database reader on the results, bring the fields back
using a List <T> using an object with property get/set and bind the List <T>
objects to the control.
 
M

Mr. Arnold

Nick said:
Thanks for your help (again). I could use a reader, but would it then
automatically generate the updates when I call SaveChanges? I was using a
view, but I'd prefer not to have to use stored procedures for the updates.

The ESQL can only sequential read forward data it doesn't have the ability
insert, update or delete at this time.

What I am telling you is use ESQL like T-SQL with a database-reader using a
DTO (Data Transfer Object) call it Nick if you like with only the fields
from the two tables, like you would do if you were doing T-SQL with
in-line code or an sproc, but you not using either one you're using ESQL.
Nick DTO would only have property get/set for the field types you want from
the result-set.


public List<Nick> GetData()
{
var nicks = new List<Nick>();

string ESQL = Navigate vs. Join

while dbreader !EOF
{
var nick = new Nick();
poputale nick with dbreader fields
nicks.Add(nick);
}

return nicks;
}

Bind nicks to the control.

http://blogs.msdn.com/zlatkom/archive/2007/07/10/entity-sql.aspx

ESQL starting in chapter 7.

If you can't download it here, then try another site.

http://www.netbks.com/database/pro-linq-object-relational-mapping-in-c-2008_6261.html

I suggest you bring Entity key to datagrid.

Then go get the entity using the datagrid by entity id. For me, it wouldn't
be a datagrid it would only be a ListBox for selection with an Edit form to
edit the entity and save the entity back to the model.
 
F

Frans Bouma [C# MVP]

Nick said:
Hello,

I've read through some articles and forums, but I'm still not sure what the
best solution is. I've got two tables, let's say TableA and TableB. It's a
one to many relationship from A to B. I want to do a join and display the
results in a datagridview. When I do the join I'll be limiting the results
based on a foreign key in TableB, so that it ends up being one to one. I hope
I'm making sense. I want to display the results of the join (both tables) in
the grid. How can I do this?

The keys are not the same, so I can't use entity splitting, right? I don't
want to allow the user to edit TableA, just TableB.

Here is some more info. The main table in my database is "Document" and
there is another table "GlobalVariables" (TableA). A document can write over
the global variable with a record in the "DocumentVariable" table (TableB).
So the DocumentVariable table has a GlobalVariableID and DocumentID. For a
given document I want to show the DocumentVariable records joined with the
GlobalVariable record so the user can see the global variable value and at
the same time show the value they are using. The global value in this screen
is not editable.

A 1:n B, so you need a master-detail setup, where A is given readonly
and B is given in a list/grid/whatever. The B's are inside the
collection in the selected A instance. Changing values in the B
instances makes them 'dirty' so when you persist the whole graph again
(all A's which will make their B's save too) you save all changes in the
B's.

Don't use a join, as you then end up with duplicate data for A. You
have entities, use them. :)

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#)
------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top