• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 872
  • Last Modified:

Linq to sql query

Hi,

I have a table that holds history of actvities on another table.  So whenever I delete a row on a table (document table) I save that action into the hsitory table (documentHistory).  Now I have something like the following (id is from documentHistory, title is from document, action is from xref_documentHistory accessed via a foreign key and historyDate is from  documentHistory)

id    title                action             historyDate
====================================
1      asset A         created           14/08/2012
2      asset B         created           18/08/2012
3      asset B         modified         20/08/2012
4      asset B         modified         28/08/2012
5      asset C         created            21/08/2012

I would like a query that returns each asset with action only once and select the latest date, so assetB above with modified action will reutrn only once in the result with the date 28/08/2012.  I am using Entity Framework and VB.NET .net 4

Cheers
H
0
gbzhhu
Asked:
gbzhhu
  • 11
  • 11
  • 3
  • +1
3 Solutions
 
CluskittCommented:
SELECT title,action,MAX(historyDate) historyDate
FROM MyTable
GROUP BY title,action
0
 
gbzhhuAuthor Commented:
Cluskitt,

Thank you for your response.  What you showed is SQL and I would prefer Linq please.  Also I need to return the id as well as the date like

1      asset A         created           14/08/2012
2      asset B         created           18/08/2012
4      asset B         modified         28/08/2012
5      asset C         created            21/08/2012
0
 
CluskittCommented:
I can't help you with linq. I hadn't noticed that you needed linq. However, to get the SQL right (with ID) you need:
SELECT MyTable.*
FROM MyTable
INNER JOIN (SELECT title tit,action act,MAX(historyDate) hd
    FROM MyTable
    GROUP BY title,action) t
ON title=tit AND action=act AND historyDate=hd
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gbzhhuAuthor Commented:
Ok I will try to translate it but there are 3 tables involved.  Real table names and relationships:

iv_asset
   id
   title

iv_asset_history
   id
   assetId                           foreign key to iv_asset.id
   assetHistoryId               foreign key to iv_xref_asset_history.id
   historyItemDate

iv_xref_asset_history
   id
   action

the data I need is

iv_asset_history.id
iv_asset.title
iv_xref_asset_history.action
iv_asset_history.historyItemDate

Thanks
0
 
CluskittCommented:
Again, this is the SQL. If you really need linq, wait for other experts to chip in.
SELECT h.id, a.title, x.action, h.historyItemDate
FROM iv_asset_history h
INNER JOIN (SELECT assetId aid, assetHistoryId ahid, MAX(historyItemDate) mhid
      FROM iv_asset_history GROUP BY assetId,assetHistoryId) t
ON assetId=aid AND assetHistoryId=ahid AND historyItemDate=mhid
INNER JOIN iv_asset a ON a.id=aid
INNER JOIN iv_xref_asset_history x ON x.id=ahid

Of course, you can change all the aliases to something that makes more sense to you, if you prefer. I, personally, prefer to use single char aliases to speed input.
0
 
gbzhhuAuthor Commented:
Cheers Cluskitt

That returns what I need, althought I get occassional repeats in the group by where there is two rows that are identical in assetId aid, assetHistoryId ahid, MAX(historyItemDate).  This is probably caused by dummy data as I can't see how that will be created in real life.

I am not having much luck in converting it to Linq.  Linqer tool may or may not convert it but I will need to buy it to try so that is out of the question for the time being at least

Let's see if anyone else can help translate

Thanks again
H
0
 
käµfm³d 👽Commented:
Try:

Dim query = From row In db.documentHistory _
            Group row By row.Title Into Group _
            Let max = Group.Max(Function(item) item.historyDate) _
            Select New With _
            { _
                .Asset = g.Key, _
                .Action = max.action, _
                .HistoryDate = max.historyDate _
            }

Open in new window

0
 
Fernando SotoCommented:
Hi gbzhhu;

If I understand the question correctly the following query should return what you need.

var results = from dh in DataContext.DocumentHistories
              group dh by new { dh.Title, dh.Action } into titleGroup
              let first = titleGroup.OrderByDescending ( hd => hd.HistoryDate ).First ( )
              select new {
                    ID = first.Id,
                    Title = titleGroup.Key.Title,
                    Action = first.Action,
                    Date = first.HistoryDate
              };

Open in new window

0
 
Fernando SotoCommented:
Hi gbzhhu;

Sorry for some reason I thought you were working in C# here is VB code.

Dim results = From dh in DocumentHistories _
              Group dh By dh.Title, dh.Action Into titleGroup = Group _
              Let first = titleGroup.OrderByDescending (Function( hd ) hd.HistoryDate ).First ( ) _
              select _
                    ID = first.Id, _
                    Title = first.Title, _
                    Action = first.Action, _
                    HDate = first.HistoryDate

Open in new window

0
 
gbzhhuAuthor Commented:
Cheers all for the quick responses.

Fernando, I have been away for a long time - many years - from EE and here you are still assisting :-)  Good to see you.  I do code in C# mainly but this project is in VB.  Your solution worked like a charm.

Thank you all again.

H
0
 
CluskittCommented:
>> That returns what I need, althought I get occassional repeats in the group by where there is two rows that are identical in assetId aid, assetHistoryId ahid, MAX(historyItemDate).  This is probably caused by dummy data as I can't see how that will be created in real life.

You should set keys in your table to prevent duplicate data like that. But, if you want to allow the occasional repeat, you can use SELECT DISTINCT (don't know the linq equivalent).
0
 
gbzhhuAuthor Commented:
Thanks Cluskitt.

I have keys on all my tables.  There is an id - autogenerated - on every table.  data is duplicate if you discount the primary key.  I think someone copied a row and pasted it in SQL management studio and so it has a unique key put all data is the same.

Cheers
H
0
 
CluskittCommented:
In that case, you should add a constraint to the table, so it won't allow those values to be duplicated. Either that, or program a check before doing the insert.

I, personally, don't use IDs on those kinds of tables. The keys are all the sensitive fields that can't be duplicated. And, to prevent duplicate data (or errors when trying to insert duplicate keys), I always check the database to see if the row already exists. If it does, I change the INSERT into an UPDATE.

In the tables you posted above, iv_asset_history has 2 foreign keys and one date. If you never want them to be repeated, you should have set the key for all 3 fields (dropping the id field). There is no point in generating an autoid that will never be used, unless you want to allow repeated rows. In fact, even if you want to allow repeats, you can create the table without any keys (I have one such table. It's rare, but they're handy on occasion).
0
 
gbzhhuAuthor Commented:
Brilliant idea!  Re: constraint or check before insert.

For I don't use IDs for these kind of tables either.  They are just connecting tables for many to many relationships


"In the tables you posted above, iv_asset_history has 2 foreign keys and one date. If you never want them to be repeated, you should have set the key for all 3 fields (dropping the id field)."  I do this usually but what do you do if you want to reference this table as a foreign key.  I am not aware of a way to create a FK to composite PK
0
 
CluskittCommented:
Create multiple FKs. After all, this table would have a composite key (2 foreign keys + date). So you'd use the same for the other one. Instead of just referencing an id (which, being an autoid, you usually don't know in advance), just use a reference with the same fields:
Table1 (FK_ID1, FK_ID2, DateField)
Table2 (Key1, Key2, FK_ID1, FK_ID2)
You can also add the date here, but that would make it redundant, that is, if you're referencing all the fields in the table as a foreign key, you might as well just remove the table in the first place.

The thing to remember is that, just like you can make a composite key, you can also make a composite foreign key.
0
 
Fernando SotoCommented:
Yes gbzhhu I am still here maybe not as often as I would like but always seeing if I can be of help to someone. Thank you from the kind words and as always glad I was able to help. Have a great day.
0
 
gbzhhuAuthor Commented:
Cluskitt,

You obviously know your databases :-)  How do I create a composite key in the GUI without typing SQL DML? I have SQL from 2000, 2005 and 2008 but can't see how to do that.  I am also not sure how Entity Framework will deal with such composite FK as it uses FKs as navigation properties
0
 
CluskittCommented:
I don't know how Entity Framework works, so I can't help you with that.

My databases don't have foreign keys defined as foreign keys. I use them as such on the queries, but all they have is an index/constraint. Any integrity issues are dealt with on a code level basis. There are some cases where we find it convenient to allow deletion of keys but leave the old value on the table with the foreign key (usually for keys that are self-descriptive or understandable on their own), while not allowing new rows to be inserted with that value.

On SSMS (at least on 2005), when you expand the table, you have the keys category. If you right click, you can insert a new foreign key. I believe it allows multiple entrances, although I haven't tested it personally.
0
 
gbzhhuAuthor Commented:
Thanks for explaining.  What I meant was for PK I can select 2 columns and together the PK.  But when I want to create a FK I have to select a column in the primary table (and not two columns) but since my primary table has 2 column primary key how is this done?  Hope we are on the same wave length
0
 
CluskittCommented:
Check this MSDN page:
http://msdn.microsoft.com/en-us/library/ms189049.aspx#SSMSProcedure

You should be able to set a composite foreign key, as long as you have a composite primary key on the origin table and have the fields for the composite foreign key on the destination table.
0
 
gbzhhuAuthor Commented:
Brilliant.  That explains well.  Never thought to select 2 columns.  Still can't think of whatv values the foreign key will contain since it points to 2 columns 8-|
0
 
CluskittCommented:
I'm not sure if I understand you, or if you're understanding my point. So I'm going to create a specific example, similar to something that happens to us:

Imagine that you have a program that keeps track of employees and their in and out times, but one that works for multiple companies. You'd have a table like:
tblEmployee (ID_Company int KEY, ID_Employee int KEY, CardNumber int, BirthDate datetime, etc)

This composite key ensures that there can be multiple employees with the same number in different companies, but each company will have unique numbers. Now we have a table for in and out times:
tblInOut (FK_ID_Company int KEY, FK_ID_Employee int KEY, InTime datetime Key, OutTime datetime)

In this case, the primary key will be composed of the composite foreign key and the InTime column. So far so good. But now imagine that you have a unique cardnumber table and that card number may be assigned to any employee on any company (when one is fired, you re-assign the card to a different one):
tblCards (CardNumber int KEY, FK_ID_Company int F_KEY, FK_ID_intEmployee F_Key)

In this case, you'll have a primary key which has to be unique, and a composite foreign key which doesn't have to be unique (not part of the key, maybe an employee can have more than one card) but has to exist on the first table. In this case, you'd create a FK relationship as described above which will ensure the integrity of the data and won't allow you to delete an employee if there's a card assigned to him.

This example isn't too common, maybe, but I think is clear enough so you can understand my meaning.
0
 
gbzhhuAuthor Commented:
Cheers for the in depth explanation.  My brain is wired in a certain way when it comes to SQL, I am not sure why :-) but this is what would come to my mind initially.  What is your opinion on that


tblEmployee (ID_Employee int KEY, FK_ID_Company int F_KEY, CardNumber int F_KEY, BirthDate datetime, etc)

tblInOut (ID int KEY, FK_ID_Employee int F_KEY, InTime datetime Key, OutTime datetime)

tblCards (CardNumber int KEY)
0
 
CluskittCommented:
You can't use employee as a key without using company as a key as well. The fact that it's also a foreign key doesn't matter. You can have employee number 1 on 10 different companies. So, if you set the key to just the employee, you will be rejecting that possibility. Also, you're limiting the number of cards each employee can have to 1. If you want to give more than one card to an employee, you can't. This example doesn't make much sense if you think of cards, but maybe it does if you think of keys (which could be cards. YAY technology ;)) or something similar.

The second table (assuming you change the key for employee and company) is something I never do. Using an ID column just for generating something unique is, imho, useless. Either your data is unique, in which case you don't need the id field (thus keeping the 3 column composite key of employee, company and intime) or you want to allow repeated columns, in which case I simply don't use a key at all. Although not common, you can just create a table without keys and create just indexes and foreign keys. I think Access doesn't allow this, but MS SQL does.
0
 
gbzhhuAuthor Commented:
Yep, your explanation makes sense.  Thank you.  I am sure I have been getting ideal and usual requirements so far and not something too complex.  I also tend to over normalise which often leads me to difficult queries.

Thank you for assisting me.  I will be looking for you here when I have a SQL question :-)

Cheers
H
0
 
CluskittCommented:
Well, I'm not an SQL expert. This is just how it seems to be easier to use. In our case, we do have multiple companies in our service, which means that most of the tables have a double key.
And I have to change table design every now and then, especially when I'm trying to do something and an expert tells me that it should be done in a different way.

Normalization is a good thing. I try to do it as much as possible, though it isn't 100% necessary. There are a couple cases where you might not want to do so, mostly for performance issues. But most of the time, I tend to do the same.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 11
  • 11
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now