I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign key in Requests Table. I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum. Here is the way I implement that. I am looking to prevent using "For each".
iRefund = (From od1 In dc.OrderDetails _ Where od1.OrderID =1 _ Select od1.UnitCost).Sum Dim objOrderDetails = (From od1 In dc.OrderDetails _ Where od1.OrderID =1 _ Select od1) For Each OrderDetail As ORM.Entities.OrderDetail In objOrderDetails If Not OrderDetail.Requests Is Nothing Then IF OrderDetail.Requests.Last.RequestType="Refund" Then iRefund -= OrderDetail.UnitCost End If End If Next
I need to charge my checkboxlist1 from database, eg load fruits and vegetables. When selecting fruits, new filter based on the previous selection in my checkboxlist2. for specific search.
I have a few tables, Listings, ListingImages and a few others related to Listings. ListingImages is related to Listings so that you can have many ListingImages per Listing.
When I query this table I do;
[code].....
Now this is fine. However, I now want to sort the ListingImages independently within each Listing (by an ImageOrder column I have in that table).
How can I do this and pass all my Includes(...). Would it be bad form to sort the ListingImages within the View as this solution seems to work?
I have a query using LINQ-to-SQL. It queries an underlying database table Rooms. It uses Where conditions to narrow down the results, namely:
Gender. Current Occupancy < Max Occupancy Available Flag is Checked
I know this should return results but it keeps returning an empty set. Any ideas? Code is below
Dim selectedHalls = (From sh In dbHalls.Rooms _ Where sh.gender = Session("gender").ToString _ Where sh.max_occupancy > sh.current_occupancy _ Where sh.is_available = 1 _ Select sh.building_name).Distinct()
UPDATE: I've verified that the issue is with the statement where sh.is_available = 1, which doesn't make sense since this is a bit field.
I can't seem to change the order of the results coming back from a linq to sql query, I want just to return the distinct values from the field x.manufacturer in alphabetical order, this is what I have and it isn't working:
public IQueryable<string> Dropone() { return (from x in CAT.CatalogueDatas where x.Parent == null orderby x.Manufacturer select x.Manufacturer).Distinct(); }
Currently I get the distinct values in non-alphabetical order, in fact I'm not sure what order they're coming out in, don't think it's the order in which they appear though.
I have an entry page that contains a datalist. I also have an ascx user control page that when it runs on page load, renders an html table template which contains category information from an SQL self referencing table. When the SQL query run on page load, it bind the results to a datalist called dlCategory, and repeats for all rows that have no value in a field in this SQL table called ParentCategoryCode. The SQL table is called ServiceRequestCategories and basically consists of CategoryCode, CategoryTitle, and CategoryDescription, and may or may not have a ParentCategoryCode associated with it.
Records without a ParentCategoryCode are top level categories, and anything that has a ParentCategoryCode are second or third level categories. The way I have the data set up, it ends up being 4 rows that are returned and rendered as 4 html tables. Each one of these is tied to the user control, and each user control runs another datalist, and shows all CategoryTitle values for each Category that has a ParentCategoryCode (second level categories). This works when you click on a LinkButton for any CategoryTitle. This all works fine.
My question is, how can I use an SQL select query to plug the value of the CategoryTitle into a label called lblCategoryBreadCrumb on my main entry page? Here is my code, but it does not seem to work.
Protected Sub ShowBreadcrumbLabel() Dim strSQL As String Dim dsData As DataSet Dim dtData As DataTable
I am trying to add some filters to a gridview that I am working on, and I'm wondering if it is possible to generate links or buttons above the gridview based on the years returned in the dataset. For example, if my dataset contains dates from 2001, 2009, and 2031 in my date column, I would like to be able to take that data from my SQL query (getting a distinct list of years is not an issue), and generate the buttons. From there, I would filter the data in the gridview based on the user clicking the buttons.
Is dynamic generation of buttons possible in ASP.NET? I do have some other ideas of how to accomplish the same functionality, but I would prefer to do it this way.
I have a array list and am using like a database trying to search for ChildID and ParentID based on certain criteria.
My example below has the following keywords to search for:
Yellow, Mustard, Honey, Orange, Flame, Talisman
I also have a category volume value which I grab the first 2 characters to determine another filter.
My code isn't returning all matches with using 'Contains'. Right now it finds the 'Orange, Flame, Talisman'/AV record and returns 50, 28. My keywords also has 'Yellow, Mustard, Honey'/AV which should return 55 and 28 as well.
HTML Code: <%@ Page Language="VB" AutoEventWireup="false" CodeFile="temp2.aspx.vb" Inherits="temp2" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
I cant seem to find a good example of how to build the query logic. We are allowing users to search based on lat/long and need to add radius as another parameter. So you enter 29.30125 as the latitude and -95.04590 as the longitude and pick say 15miles as the radius, i need to return all records that fall within that radius.. can someone suggest a good site to review this type of query?
So the BlogPostTags table only contains 2 fields, BlogPostID and TagID.
When i run the query above i get 3 results back. Same blogpost 3 times but with 1 tag in each. It should return 1 post with 3 tags. The problem lies in the Tags query above.
I have a LINQ Query that includes an order by that is based on a user preference. The user can decide if they would like the results ordered asc or desc.
I have 4 Checkboxlists, if i check 1st item of checkboxlist1 -- > populate items in checkboxlist2 if i check 2nd item in checkboxlist1 -- >populate items in checkboxlist2 with last selection as well as 1st checkboxlist items. So I need to Append items in Checkboxlist with existing items and also with previously selected Checkox. Is it any way except DataTable ?
I'm building an ASP.NET MVC site that uses LINQ to SQL. In my search method that has some required and some optional parameters, I want to build a LINQ query while testing for the existence of those optional parameters.
Here's what I'm currently thinking: using(var db = new DBDataContext()) { IQueryable<Listing> query = null; //Handle required parameter query = db.Listings.Where(l => l.Lat >= form.bounds.extent1.latitude && l.Lat <= form.bounds.extent2.latitude); //Handle optional parameter if (numStars != null) query = query.Where(l => l.Stars == (int)numStars); //Other parameters... //Execute query (does this happen here?) var result = query.ToList(); //Process query...
Will this implementation "bundle" the where clauses and then execute the bundled query? If not, how should I implement this feature?
My application includes BUILDING table with columns BUILDING_ID, NAME, TOWN_ID, I run the LINQ query listed below and need to append two letters to buildings.NAME based on TOWN_ID. I assume that I need to place an "if" or "select" statement inside the query but I can not figure out how to do that,
I have two text boxes for the user to enter UserName & First Name, both these fields are optional and not mandatory to fill.
once the user presses the Search button, i execute the following query to fetch matching records.
[Code]....
The problem is, in cases where User leaves both the textbox or one of the textbox empty/blank, in that case that particular field should be removed from where clause.
More like if something is entered then search for that particular record otherwise search for all the records.
I'm new to linq. here is the scenario i need to write query for.I have a "Candidate" table with a varchar property "Skill"I have the table OR-mapped with LINQ to SQL on dbml file. Now I want to write linq query that:Selects all Candidates that have skill containing any of the keywords from ("asp.net","php","java")