472,952 Members | 2,197 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

Historic values in LINQ

Hello, I've just started using SQL to LINQ to access my SQL Server
tables, and it's a wonderful tool. Are there any equally elegant ways to
store and access historic values for the objects?

For instance, I'm managing a Person table with mail addresses, phone
numbers etc. I would like to be able to store the earlier values of
these fields, so I can see what mail address/phone number a given Person
had at a specific point in time.

Any tips and links to guides etc. would be very appreciated!

Thanks.
Regards,
Nils Magnus
Jun 27 '08 #1
10 1251
Nils Magnus wrote:
Hello, I've just started using SQL to LINQ to access my SQL Server
tables, and it's a wonderful tool. Are there any equally elegant ways to
store and access historic values for the objects?

For instance, I'm managing a Person table with mail addresses, phone
numbers etc. I would like to be able to store the earlier values of
these fields, so I can see what mail address/phone number a given Person
had at a specific point in time.

Any tips and links to guides etc. would be very appreciated!
There is no support for historic data in the database, so you would have
to store it as regular data, and to get a time line you would add
information about the time interval when the data is valid.

As you want to be able to store multiple values for each Person record,
you need to store them in a separate table. You can either store each
piece of information in a single table by adding a field for what type
of information it is, or have separate tables for address, phone, et.c.

--
Gran Andersson
_____
http://www.guffa.com
Jun 27 '08 #2
"Nils Magnus" <nm**@nmee.netwrote in message
news:eT****************@TK2MSFTNGP06.phx.gbl...
Hello, I've just started using SQL to LINQ to access my SQL Server tables,
and it's a wonderful tool. Are there any equally elegant ways to store and
access historic values for the objects?

For instance, I'm managing a Person table with mail addresses, phone
numbers etc. I would like to be able to store the earlier values of these
fields, so I can see what mail address/phone number a given Person had at
a specific point in time.

Any tips and links to guides etc. would be very appreciated!

Thanks.

Regards,
Nils Magnus
{X-posted to cljd for extra comment]

As Gran pointed out there is no explicit support for this in a database,
because this historical information is itself data. That is, it's not (in
this scenario) metadata, because you specifically stated "so I can see what
mail address/phone number a given Person had at a specific point in time."

Since you're planning an address book, one thing you've likely already
considered is that a Person may have multiple _current_ addresses. In fact,
since one Address may also be associated with multiple Persons, you have a
many-to-many relationship, usually best implemented as a join table with at
least one attribute to describe the "name" of the relationship between that
Person and that Address. For example, "summer cottage".

For your purposes you could add the date information to that join table. Or
more realistically, have fields for start and end dates, but assume that
these fields will often be empty. After all, how many people have this kind
of data for their contacts? Without some research I couldn't even tell you
what *my* addresses have been to an accuracy of a month, let alone exact
dates.

If doing just the above, you'd then likely want a current flag in that join
table also, which would indicate which Person-Address combinations are
current. At this stage you'd be able to say, at a minimum, this Person
presently has these addresses, but at various times in the past they have
also had these addresses.

An interesting situation arises at this point - what happens if a Person was
once at Address A, then afterwards was at Address B, but now is back at
Address A? We've already established that start and end dates may be
unobtainable, so distinguishing between "Person-Address A Now" and
"Person-Address A Then" cannot depend on those fields. One way around this
is to add yet another field to the join table, which would participate along
with the Person ID and the Address ID in the join table primary key. I
myself would assign no extra meaning to this field, except that it be unique
for a given address. At this stage you'd be able to say, at a minimum, this
Person presently has these addresses, but at various times in the past
they've had these others, and I can see that one of the current addresses
was also a past address (for example).

Finally, because it very often _is_ quite easy to say, "I don't know exactly
_when_ Arved lived at addresses A, B, and C, but I do know that he lived at
A, then B, then A again, and now he's at C", you could have one final field
in the join table, which is a date sequence number (for lack of a better
term). Since re-arrangement operations on this field (changes due to past
address inserts etc) will be infrequent, your numbering convention is not
critical. If using this scheme you will however have to have a "don't know"
value.

At this (rather elaborate) stage you could say that this Person currently
has these addresses, but at various times in the past they've had these
others, one of the current addresses was a past address, and another past
address was used twice. Not only that, I know (because of the date sequence
number) how long (relatively) the person has had the current addresses, and
for 3 of the 5 past addresses I know which came before or after.

For phone numbers and email addresses I myself would not use such a detailed
system. Strictly speaking both also participate in many-to-many
relationships with Person, particularly PhoneNumber, but I myself would be
satisfied with the information that such-and-such a Person currently has
EmailAddress EA1 and EA2, and in the past has also had EA3 and EA4. So the
start date, end date and date sequence fields could be omitted in these join
tables.

This is obviously open to debate. It's merely a system I might consider had
I to implement your requirement.

AHS
Jun 27 '08 #3
You need to model it.

Person 0..* ----- 0..* Address

When you refer to something refer to both the person and the address at
which they received the goods (or whatever).
--
Pete
=========================================
I use Enterprise Core Objects (Domain driven design)
http://www.capableobjects.com/
=========================================
Jun 27 '08 #4
Lew
Arved Sandstrom wrote:
This is obviously open to debate. It's merely a system I might consider had
I to implement your requirement.
This type of data modeling cries out for a standard data-warehouse star
schema, with fact and dimension tables.

--
Lew
Jun 27 '08 #5
Arved Sandstrom wrote:
{X-posted to cljd for extra comment]
Why cljd and not any other database group ??

Arne
Jun 27 '08 #6
"Arne Vajhj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk...
Arved Sandstrom wrote:
>{X-posted to cljd for extra comment]

Why cljd and not any other database group ??

Arne
Well, it's the only database-oriented NG I follow, and I thought that
getting the subject into at least one such NG would be useful.

In hindsight it's a topic that, suitably reworded to make it somewhat more
encompassing, would be interesting in a more general database-oriented
group.

AHS
Jun 27 '08 #7
"Lew" <co****@lewscanon.com.invalidwrote in message
news:EN******************************@comcast.com. ..
Arved Sandstrom wrote:
>This is obviously open to debate. It's merely a system I might consider
had I to implement your requirement.

This type of data modeling cries out for a standard data-warehouse star
schema, with fact and dimension tables.
--
Lew
Agreed.

AHS
Jun 27 '08 #8
Arved Sandstrom wrote:
"Arne Vajhj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk...
>Arved Sandstrom wrote:
>>{X-posted to cljd for extra comment]
Why cljd and not any other database group ??

Well, it's the only database-oriented NG I follow, and I thought that
getting the subject into at least one such NG would be useful.

In hindsight it's a topic that, suitably reworded to make it somewhat more
encompassing, would be interesting in a more general database-oriented
group.
microsoft.public.sqlserver.programming
microsoft.public.dotnet.framework.adonet
comp.databases.sqlserver

were some of the other possibilities.

But it is not obvious exactly where the questions belongs.

I was just puzzled that you picked the Java one.

Arne
Jun 27 '08 #9
Nils Magnus wrote:
Hello, I've just started using SQL to LINQ to access my SQL Server
tables, and it's a wonderful tool. Are there any equally elegant ways to
store and access historic values for the objects?

For instance, I'm managing a Person table with mail addresses, phone
numbers etc. I would like to be able to store the earlier values of
these fields, so I can see what mail address/phone number a given Person
had at a specific point in time.
I've had to do this in a medical context (where this stuff is hugely
important, together with information about who made which changes), so I
guess I can be pretty helpful here. I've not played with the guts of
Linq-to-SQL - it's too much of a step backwards for me... the Entity
Framework looks better - version 2 of the Entity Framework even looks
like it'll support POCO, so it might even compete with NHibernate at
that point.

In NHibernate 2 (still technically in alpha, but it's pretty stable in
my opinion), you can add an event listener so you can be notified when
any entity is pushed from your CLR objects into the database and attach
history at that point. This works well in my experience.

Alun Harford
Jun 27 '08 #10
Please go to http://www.a2zdotnet.com/Default.aspx
for step by step tutorials for beginers in LINQ sections.

--
regards,
Pankaj
http://www.A2ZDotNet.com
"Nils Magnus" wrote:
Hello, I've just started using SQL to LINQ to access my SQL Server
tables, and it's a wonderful tool. Are there any equally elegant ways to
store and access historic values for the objects?

For instance, I'm managing a Person table with mail addresses, phone
numbers etc. I would like to be able to store the earlier values of
these fields, so I can see what mail address/phone number a given Person
had at a specific point in time.

Any tips and links to guides etc. would be very appreciated!

Thanks.
Regards,
Nils Magnus
Aug 11 '08 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Carsten Kring | last post by:
We are developing a sevlet-based web-application which will supply the user with real time and historic currency information. For the historic currency information we need a graph Java applet: ...
1
by: Ronald S. Cook | last post by:
We have a table "Animal" that contains the column "AnimalNumber" with values like these: 100001 100002 100003 1XAB 10E43ABC 11RU5V
9
by: =?Utf-8?B?cmF1bGF2aQ==?= | last post by:
Hi all: after reading different places/sites about linq... I ran into these questions: 1. What framework do we need to run linq ? (does it depend on what version of visual studio we have?) how...
2
by: =?Utf-8?B?UGhpbGlw?= | last post by:
I am attempting to insert a simple record with LinqDataSource from a ListView, however I always get a message saying "....LinqDataSource 'dataSource' has no values to insert. Check that the...
0
by: =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post by:
I'm encountering some strange behavior after deploying a ASP.net 3.5 website to production, i'm unable to reproduce these in my dev environment. This error seems to occur very randomly but it's...
0
by: chandrasekhar | last post by:
Hi I have developed a web page with some controls. I put all of them in session variables. I passed this values in the page. When an user enter some data in form fields and click the buttton ,...
4
by: =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post by:
We’re storing our main entity in an insert only table which stores the history of past revisions, but we’re facing problems with storing this history as LINQ will only update the entity, and...
14
by: thj | last post by:
Hi, I was wondering what you guys are using and why? LINQ to SQL or NHibernate? Thanks in advance, Tommy
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.