Need to figure out the correct way to do a simple SUM function in Linq, but also manipulate one of the feilds coming back by doing a Substring on it, here is the original SQL query:
[Code]....
if LINQ to Entity queries the EDMX class or its .CSDL or SSDL XML on its way to getting data from the RDBMS?
View 8 RepliesI've got a query such as
Dim MediaQuery =
From m
In dB.DOWNLOADS _Where m.ID = id _Select
which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE
However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?
select * from MyTable select "MonthName"= case when datepart(month,date1) =1 then 'January' when datepart(month,date1) =2 then 'February' end , count(*) as CountByMonth from venkat group by datepart(month,date1)
View 1 RepliesI am using the following LINQ to entity to get data from my db.
It should return only one row. So i want to be able to get the first row in the set and not have to go through a foreach.
I would also like to check and make sure it is only returning one row, if it returns multiple i need to throw an exception
[Code]....
select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 RepliesI have something like the following in pages in two different projects, which saves to a DB with a LINQ to SQL operation:
[Code]....
One of the pages works with this code and the other doesn't. The only difference is that the project in which it does work employs a data control tool (ListView) and a custom profile provider.
Hi guys I need to rewrite the sql query below using Linq to entity. Unfortunately I don't have a lot of experience of using Linq.
With TempTableName AS
(SELECT [ColumnName],
[ColumnName2],
[ColumnName3],
[ColumnName4],
ROW_NUMBER() OVER (order by ColumnName desc) as RowNumber from TableName )
SELECT
[ColumnName],
[ColumnName2],
[ColumnName3],
[ColumnName4]
FROM TempTableName WHERE ROWNUMBER
Between 10 and 100
I have a table with columns A and B, values as follows..
A B
-- --
1 11
1 12
1 13
2 21
2 22
2 23
How I can write following SQL query using LINQ. There is no default constraint define for the ID column so we have explicitly pass the NEWID().
I want to replicate the products of a company to another company.
Insert Into Products Select NEWID() AS ProductID, ProductName, CompanyID = @DestinationID, ProductCategoryID From Products Where CompanyID = @SourceID
Also once I get these result how can I use InsertAllOnSubmit to insert all the records at once without looping it.
I have the following code which writes to a Gridview:
[Code]....
Using the same methods of connecting to the database, how can I amend this code to write the result of a second query (which only returns a number) to a label?
i have a table for customer forecast with following fields.. customer, year, week, quantity. now i need to write the SP to retrive the records based on the from week,year and to week,year... like this... from 45th week of 2009 to 25th week of 2010. how can write the query...
View 4 Replieshow do i write my alter schema query in Asp.net
using the sqlclient object..
I have a button click event that adds a record to a table based on the userid and threadid the user is viewing. I am a bit new to entity so I am stumbling around. I have to where conditions
ThreadID == threadid
UserId == userIdPosts
I have this query but do not know how to execute it and if I have it as is I get this in the yellow screen of death Both the theadid and userIdPosts have data in them.
var query = context.TrackLists.First(t => t.ThreadID == threadid);
context.TrackLists.First(t => t.UserId == userIdPosts);
Sequence contains no elements
Line 90: // find is user has already tracked this thread.
Line 91: var query = context.TrackLists.First(t => t.ThreadID == threadid);
Line 92: context.TrackLists.First(t => t.UserId == userIdPosts);
I have a web app for our golf club. When I compute handicap index for each golfer, I have to select the most recent scores and then a subset of those scores depending on how many rounds of golf the golfer has played. All the scores are entered into a single SQL Express table called "Rounds". Verbally, this is what I'm trying to do:
1) select the twenty most recent golf scores (sort on date descending, "take(20)") [if less than 20 records, then select all available];
2) for this set of records, select the 10 lowest scores (or smaller number if golfer has less than 20 rounds);
3) compute the average round differential for the subset of records, etc. to calculate handicap index (this step is working ok...)
My current VB code has this LINQ query (which is flawed -- it selects the lowest handicap differential scores of ALL records for the filtered user):
[Code]....
How do I modify this query to accomplish items 1) & 2) above? It seems this should be simple, but my experience with queries is still limited.
I am having a meltdown over something that seems so simple and yet isn't working. Here's my scenario.
I have an object structure of tEvents, which contains properties of an event like a run of concerts. tEvents in turn contains an entity set of tEventOptions, which include properties like EventDate, Cancelled etc. I would like to query a list of tEvents using properties of the tEventOptions, for example filtering by date.
My pageis using a calendar object to show event dates. So I am trying to find if any tEvents in the List coming back from the database match the date of the day element being rendered in my Calendar control like so (in this snip 'data' is List and results from a db query):
[code]....
Except r, my query result var, is always true no matter what data it receives. I have tried the sub query using other properties of tEventOption and get the same 'true' result each time. I know that the data does not reflect this result so I clearly have a problem with the structure of my query, but for the life of me I cannot find a way to resolve it.
I have this query for getting data through Entity Framework, which works today. Thing is, i want to create the query dynamically, i.e build it up in blocks, but i can't figure out how exactly to do it. I believe if i can somehow save one of the values that i retrieve in the beginning i can do it, but now i retrieve it twice (probably a bit slow as well? Unless the compiler fixes it before quering). Makes sense? Here is the LINQ:
[code]....
I.e based on if various variables have values.
Best way to show the SQL trace of a LINQ query to Entity Framework 3.5? I am using ASP.net and EF 3.5.
Dim dbo As Web.Portal.RBMEntities = New Web.Portal.RBMEntities
Dim Query = From RoleAllocations In dbo.RoleAllocations Where RoleAllocations.user_id = _ID And RoleAllocations.expire_date > Today Select RoleAllocations
Console write the SQL trace?
I have couple of DDL's which let user choose couple of different values from it. Once the user has made his selection he clicks on the "Submit" buttion which redirects the user to the other page, which then displays table from backend SQL DB. The pages are named Page1.aspx and Page2.aspx resp. I am using Querystring to carry forward the values of the DDL's to Page2.aspx.
How can i modify the query in such a way that even if one of the DDL's is left blank the Query would ignore that and shall produce the table considering value of DDL2 in the where clause. In case both the DDL's are empty then the query will not have a where clause and shall showcase the complete table as such. I've written a query for this but it's not working the way it shoul.Here is my code for the SQLDataSource: [Code]....
How to write select query to get counts in row values
View 6 RepliesI have a user control, named productslist.ascx. On pruductslist.ascx, I am using Entity Datasource and a QUERY EXTENDER and LISTview. I have a page Defualt.aspx. On Defualt.aspx, I have a TEXTBOX with a SUBMIT button for searching the site. Everthing works fine so far, AS LONG as the results of the Search is posted back to Default.aspx. What I want is for the results of the search to be posted on a different page.
So I created a new page, Results.aspx and set the postback url to it.
What I want is that when a user clicks the Submit button, after entering a search string, the text the entered is used to execute the query on Productslist.ascx and the results displayed on RESULTS.ASPX. Again, everything works fine as long as the results are posted on same page hosting the usercontrol, BUT I want the postback url to be a different page. I am coding in VB and VS 2010.
I have a "simple" method that takes a Linq entity containing the information I want to update. The method then creates a list of counties the information is to be updated for, and tries to update the information for each county.
So user goes in fills form out selects update All counties, and the information in the form should be updated for all the counties for the member. Here is the code.
[Code]....
The first pass through the foreach runs perfectly, the second time through I get an error: Cannot attach an entity that already exists.
What is best way to work around this?
I have though about putting the foreach loop in my business_logic layer, and have it pass both the newRecord and OldRecord, but figured I would ask, before making the code change.
I am new to this, and I am trying to implement a linq query similar to db.Genres.Include("Albums") command from
mvc music store using my current DB setup. I realize that the tutorial mentions Checlking the "Include foreign key columns in the method" when creating the tables in ADO.NET, but if I am using LINQ to SQL classes, then how can I obtain the same effect. And how do I use
db.Genres.Include("Albums") to simplify my life?
i have a table for customer forecast which contains customer, week number and quantit as columns. now i need to write query and list the week as column for each customer and each cell should have a quantity. how to write query for this using pivot logic. i dont want to use cursor.
View 3 RepliesI´m using Linq to Sql in my asp.net application, but the problem is:I have a query like this:
[Code]....
My query is not returning anything, how do I do to get this?my problem is with this interval I need to have.