.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

LinqToSql: Calculated Field Question...

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :ASP.Net

Hi There,


Im currently using LinqToSql to get a list of groups from the database. (tblGroups)

In code, im iterating through my collection (tblGroups) and counting sub-elements of tblGroups and storing the whole object in a DTO (simple object) with an additional property (UserCount).

When these are stored in a simple object, the UserCount is worked out by looking through each group and counting the users inside and making sure they fit a certain role.


This starts to hamper performance when I have so many groups and i would like to take all of this to the database layer and work it all out initially.


My question is:

What is the best way to achieve this? I would ideally like to remove the DTO as this adds a whole new layer. Could I add a new property to the database (like a calculated field) that is populated everytime this info is request as say: a stored procedure?





View Complete Post

More Related Resource Links

Calculated field Sorting Now Working , but have performance question


 Thanks to mitja.GTI.

My sorting is working ...

although i had to take the table that has my calculated column and make a view (so i could filter) and then bind the gridview to the view.

My question is as this.

1.) in order to get my calculated field i had to load my data into a table (as suggested)

2.)Add a column to the table and perform calculations

3.)make a dataview from the tables because i could not get the Gridview.datasource= ds.tables("loads").select("Deadhead <= " & sngRadius

4.)the largest of the 3 tables my query is based on contains about 7000 records. The way i have the logic & programming now everytime a new "truck location" is put in the textbox1 and "find loads/button1" is clicked. It calculates the deadhead miles of all 7000 records before filtering. I am filtering based on the deadhead miles.

Does anyone have any recomendation about how to speed this up a bit.

Also in order to get paging to work i had to rebind/filter the data again ... means looping through all 7000 records each time paging is called. This seems terribly ineffecient as i am already forced to use access instead of SQL.


Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

Use Sharepoint Calendar Start Time field in calculated column

I'm trying to limit the calendar view to current week using the Sharepoint defined field Start Time. However, when I display the serial number behind the Start Time it looks like it is actually giving me the serial number for the Created Date field. I believe it is because the appointment is reoccurring but I'm hoping there is a way around it. I created a new testdate field and manually entered the date and set those appointments as all day events however they are not reocurring appointments. The testdate returns the correct serial number however the start time returns an incorrect serial number. SerialNumber1=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])) SerialNumber2=DATE(YEAR([testdate]),MONTH([testdate]),DAY([testdate])) Start Time  SerialNumber1 Testdate  SerialNumber2 8/31/2010 40420 8/31/2010 40421 8/31/2010* 40405    9/1/2010* 40405    9/2/2010* 40405    9/7/2010 40427 9/7/2010 40428 9/13/2010* 40405    9/14/2010 40434 9/14/2010 40435 9/14/2010* 40405    9/15/2010* 40405    *These are reoccurring appointments scheduled as all day events. The others were entered as one time appointments scheduled as all day events.

Showing highlighted text (HTML) in calculated field?

Greetings all,   We're porting our 2007 list to 2010. In Sharepoint 2007, we used the following Calculated Column code to display a highlight over items that had different statuses:   =IF(Date="","<DIV style='background-color:#ff6666'>Not <b>Yet</b> Set</DIV>", IF(Finalized=TRUE,"<DIV style='background-color:#66ff66'>Finalized</DIV>", "<DIV style='background-color:#fffa91'>Tentative</DIV>"))   When we use this in Sharepoint 2010, it doesn't seem to apply the DIV structure. Any ideas of how we can change our code to make this work? Thanks! --Dave   No matter -- I need to add a code snipped ( http://marijnsomers.blogspot.com/2010/01/write-html-code-in-sharepoint-via.html ) to the page... thanks!

Search field question

hi guysin my database i have one table that has basic patient information (id,name,dob etc, visit date etc)i would like for the user to be able to search for a patient based on his unique personal id number and return the fields i specify (name,lastname) in the gridview. my problem in that one patient may have multiple visit dates. i would like the gridview to return the following as an exampleName     Lastname   Visit dateJoe         Johnson      10/05/10Joe         Johnson      13/08/10joe         Johnson       20/09/10from that the user will be able to click on the visit date that he wants to see the details aboutmy problem in that since the user ID is a unique field in my table i dont know how to create multiple visit dates. i think i should put the visit date in a different table and create a relationship but then i dont know how to present details from 2 tables in one gridviewthank you

Calculated Value based on a Checkbox field

Hi All:         This should be quite straightforward, but for some reason...         I implement a calculated value with this expression: =IF(fieldName="Yes",1,0) and the data type returned from this formula is a number with 0 decimal places...         Something particular about this, is that the fieldName field is a checkbox.         It doesn't work, I only get 0s for all the values (Yes or No).         I tryed again changing to =IF(fieldName="Yes","1","0") and data type returned string, but I get the same result...         Can you realize what I am doing wrong?... It's just that I see it pretty well...         Ok, thanks.Acaspita

Why can I not group by calculated field in Date format?

After migrating to SP2010 Foundation, I've discovered that for some reason I can't group a list by a calculated date field. Ex: Create a list with a date column A. Create another column B with a simple calculated date of [A]+1 and use the field format type 'date' Modify view to group the list by column B.  You're list will load, but when you attempt to expand one of the groups you'll be greated with an exception error. Why?? Am I doing something wrong? Note: You can group fine if the data type for column B is anything except 'date' format, but I really need to group items by a calulated date. Ultimately, what I'm trying to do is group my list items by week by calculating the end of the week using a simple formula =[Must ship by]+7-WEEKDAY([Must ship by])  then I want to gropu by the [Must Ship by] column like I always did in SP 2007, but now it errors out.  I also wanted to create my own timesheet template since the Fab 40 templates aren't available for 2010, but I can't really do that effectively without being able to group timesheet entries by week.  

Calculated column and hyperlink field type

Hi I have customer who is asking me if this is possible to provide filter based on the HyperLink field, where he could easly limit results to only those items where link field is filled. As filtering on hyperlink column type is not supported I was thinking to buld some calculated field based on it to return YES (if link is provided) or NO (if link is not provided). The problem is that hyperlink field type is not avialiable on the list of field to be used on calculated field. is there some workaround for that ? thanks for help   Mariusz Gorzoch Blog : http://mariusz.gorzoch.pl Twitter : http://twitter.com/MariuszGorzoch Facebook: http://www.facebook.com/mariusz.gorzoch

infopath 2007 Expected value type: end-of-string ! calculated field

Hello I am using infopath since there is no field with type date and time in InfoPath 2007. I created three fields one for date one, for hours, and one for minutes then I created forth field that concatenate the values of the three fields this field will be used as column in form template library used by my form. All seems good so far but when I tried to concatenate those fields go the following error Expected value type: end-of-string how can I solve this ? my xpath looks like ../my:calmomoDate../my:cbmomoHours:../my:cbmomoMunites Thanks  Momo

Calculated field for weekday not correct


We use a calendar on Sharpeoint to track events. These events can be all day events that may start at 12:00 am on Thursday and go until 11:59 pm on Friday. I put in a calculated field based on start time so it displays the day of week the event starts along with start time of the event in a list.
This is the calculation I'm using =TEXT(WEEKDAY(([Start Time])),"ddd") It works great if the start time is after 12:01 am. If it is 12:00 am, it displays the day prior. For example Start time of 9/17/10 at 12:00 am will display the day as Thursday.

Anyone know how to correct this?


Filtered Calculated Field


Hi all,

I'm new working with SSRS. I have a doubt, it is possible to create a Filtered Calculated Field. For example: I have a dimension Product (contains: Car, Computer, TV...) and a measure Revenues. But i want to create a measure Car Revenues and other measure Computer Revenues. And Car Revenues represents only the Revenus from Car and Computer Revenues Represent only the Revenues from computer.

Is this possible?

Thanks in advance.

Jorge OS

Calculated field error



 tried to add a Calculated filed for cumulative total

with the following expression =runningvalue(fields!ReceiptQty.Value,sum,nothing)

it is giving error like

"An error accurred during local report processing.  The definition of report is not valid.

An internal error occured on the report server. See the error log for more details"

 Pls somebody help what is or where am I wrong????

Problem with calculated field in SSRS

i have the foll scenario:
col1 col2 col3 col4 
A   1  100 
   2  250 150 
   3  500 250 
   4  600 100 
   5  900 300 

For col4 i need to calculate the difference b/w (the value in current row in col3 i.e. 250) - (the value in the previous row in col 3 i.e.100). i need to do that for all rows of col4. I need help with the expression to do this in SSRS.

Question about using LINQtoSQL or LINQtoStoredProcedure



I am starting a new project and it will be my first time using LINQ. I know that regular stored procedure in SQL Server is very fast. I am thinking of using LINQtoSQL and I know it offer protection against SQL injection. Here is my question.

Would LINQtoStoredProcedure offer the same project against SQL injection? Is it faster than LINQtoSQL? If LINQtoStoredProcedure preferrred? and why?

I don't want to make a mistake and later realized that I made the wrong choice. If any experts here can offer any experienced advise, it is great apprecited by this newbie.

Thanks for reading my post....

Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c


I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

Adding a calculated field to a databound control


I'm using a gridview to extract a set of records. I need to add another field to the gridview, which can calculate the difference between a date associated with each record and the current date. Ideas?

Discrepancy in Calculated Field using Date/Time



I have a calendar item as part of my MOSS 2007 site and would like to show the events coming up in the next 30 days on my home page.  After some research I understand that I cannot filter on the Start Time field when I try to create a view, so I've created a new field called Event Begin that is a calculated field set to =[Start Time].  I can then create my view for the upcoming events by filtering on the calculated Event Begin column.


This works well, except for events that are marked as All Day.  In this case, the value of the Start Time shows midnight as the time (i.e. 1/11/2008 12:00 AM) which is fine, but the value of the Event Begin column shows the time 5 hours earlier (i.e. 1/10/2008 7:00 PM).  I am not subtracting anything when calculating the Event Begin value, so my initial thought is that I have a conflict between Time Zone settings somewhere.


I am Eastern Standard Time here in the US which is GMT-5:00, which would explain the 5 hour difference.  Unfortunately, I cannot figure out where the incorrect Time Zone setting might be.  Within my site the time zone is correct (under Site Settings >

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend