Entity Query - Combine Into One And Avoid Merging The Results
Nov 6, 2010
I can't seem to figure out how to write this query properly. I've tried various combinations but nothing's worked yet. Below is the relevant portion of my database model: I need to select the products that match a given Category and Group, and that match a given Year, Make, Model, submodel. This I've done below:
ItemList = From P In gDataContext.Products.Include("Groups").Include("Groups.Category1").Include("LookupYearMakeModels") From G In P.Groups Where G.Category = Cat And G.Grp = Group From Y In P.LookupYearMakeModels Where Y.Year = YMM.Year And Y.Make = YMM.Make And Y.Model = YMM.Model And Y.Submodel = YMM.Submodel Select P
I now also have to select products that match the Category, and Group but are Universal (Product.Univeral = True). I'm currently writing two queries, the one above and the one below. I'm merging the results of the two by simply using ItemList.AddRange(ItemList2) ItemList2 = From P In gDataContext.Products.Include("Groups").Include("Groups.Category1") where P.Universal From G In P.Groups Where G.Category = Cat And G.Grp = Group Select P But I want to combine both queries into one and avoid merging the results. How can I do it?
View 2 Replies
Similar Messages:
Mar 14, 2011
I have a simple Entity query:
[code]....
I then check whether one of the result values is more recent than six months ago using an if..then like so:
If rhcexists.First.SignatureDate > Date.Today.AddMonths(-6) Then
End If
However, if there are no results returned this will return an error. Is there a way for me to tell it to act as if the date is older than six months if there is no value at all? e.g., could I in pseudo do something like:
If Exists(rhcexists.First.SignatureDate, Date.Today.AddMonths(-8)) > Date.Today.AddMonths(-6) Then
End If
View 2 Replies
Sep 15, 2010
What would it be the simplest way to merge a result like this from a sql query to be displayed in an asp.net gridview?
NULL Tarde Fer W. Lunes
Mañana NULL Fer W. Lunes
I need the result to look like this
Mañana Tarde Fer W. Lunes
View 2 Replies
Feb 25, 2010
Is there a way to combine the LINQ queries together and have access to both of the results independently?
[Code]....
View 10 Replies
Mar 9, 2011
I have a repeater like so:
[Code]....
Problem is, I also want to show the viewer the available number of spots in the room. But this requires somehow pull in either the current_occupancy / max_occupancy or in performing a calculation (e.g. max_occupancy - current_occupancy = actual_available) and then returning that with the room.
The end result I'm looking for is to return each room in a button control with text that looks like this:
"Room 1 - 2 Open" "Room 8 - 1 Open" and so on
View 2 Replies
Apr 1, 2010
Is there any way to combine multiple Audio Files (.wav etc) into one file?
I have found an example here :
[URL]
Quote:
[code]....
View 1 Replies
Mar 7, 2011
I'm new to ASP.NET MVC and I'm trying to implement an address manager.I'm using Linq2Entity Framework. I've got a table with contacts and one with phone numbers. These two tables are linked by a contact_has_phone_number table. The Entity Framework enables me to get all phone numbers that are assigned to one contact. This is done by calling contact.PhoneNumbers which returns a collection of phone numbers.
View 1 Replies
Jun 9, 2010
I have a weired problem. One of the pages in my application show's status of tasks in a table. I am using entity framework ( entity to object) to do all the data handling with DB. I noticed that even though the status is updated in DB, its not reflected on my webpage even after continuous refresh! If i stop the server and start the application again (currently running on VS2008 dev server) the updated data is displayed!
Am I seeing the cached data? how do I prevent this from happening?
View 1 Replies
Feb 11, 2011
I have an ASP.NET 4 web app that is using the Entity Framework to hold, amongst other things, a hierarchical structure of items that is used for features such as navigation.
One of the functions of the application allows the export of a particular branch of the hierarchy tree to XML, generated using a DataContractSerializer. Once exported, the intention is that the XML can then be uploaded to another instance of the same app, deserialized back into EF objects and then added to the data model on the second server.
When I try and import data in this way, I'm finding that I see the following error:
The object could not be added or attached because its EntityReference has an EntityKey property value that does not match the EntityKey for this object.
Taking a closer look at the XML that is generated by the serializer, I can see that the entity key is among the items that has been serialized. Since I'm importing entities that originate from a different database, some of the ID's clash.
What I'd ideally like to be able to do (unless I'm missing something here) is attach the imported entities to an object context and reset the ID's of the objects being imported.
Is there a smart way to do this? Do I simply have to set the ID for each of my imported entities to 0, attach them to the object context and then save changes?
View 1 Replies
Jan 21, 2010
I'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?
View 2 Replies
Mar 14, 2011
I have a simple If..Then like so:
If rhcexists.First.SignatureDate > Date.Today.AddMonths(-6) Then
End If
rhcexists is a simple query to the Entity Model:
Dim rhcexists = From p In dbContracts.Signatures _
Where p.StudentID = CStr(Session("people_code_id")) _
And p.ContractType = "rhc" _
Order By p.ID Descending _
Select p
Problem is that this comparison results in an error:
LINQ to Entities does not recognize the method 'System.Object get_Item(System.String)' method, and this method cannot be translated into a store expression.
This is occurring on the If clause. Any ideas why this occurring and how I can fix it?
I am using ASP.NET 4, EF 4, and VS 2010 SP1.
View 1 Replies
Jun 18, 2010
I have a java application that send to a aspx page the user name and password in a Query String. How can I authenticate the user in this aspx web page, and how can I avoid that a user copy the link in the internet explore and acces to the page.
View 1 Replies
Dec 11, 2010
I have applied a filter on a page using drop downs.
When i click on apply filter.. the page gets filtered. Now i click on the edit of filtered result.. which redirects me to another page.
I want to send the selected data from the drop downs from the filter page to the redirected page.. and again to the filter page.. to retain the filter. I am doing all this by sending the values in Query string.
Is there anyway.. i can avoid the visibility of the query string in the address bar and still get the values and required result.
View 2 Replies
Jun 23, 2010
I have a Dictionary<string, bool> where key - control's ID and value - it's visible status to set:
var dic = new Dictionary<string, bool>
{
{ "rowFoo", true},
{ "rowBar", false },
...
};
Some of controls can be null, i.e. dic.ToDictionary(k => this.FindControl(k), v => v) will not work because key can't be null. I can do next:
dic
.Where(p => this.FindControl(p.Key) != null)
.ForEach(p => this.FindControl(p.Key).Visible = p.Value); // my own extension method
but this will call FindControl() twice for each key. How to avoid double search and select only those keys for which appropriate control exists? Something like:
var c= FindControl(p.Key);
if (c!= null)
return c;
but using LINQ.
View 4 Replies
Jan 22, 2011
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.
View 2 Replies
Mar 17, 2011
datacontextclass dc=new datacontextclass ();
var news= dc.GetNewsCompany(Int64.Parse ( _idCompany));
if (news.GetEnumerator().MoveNext())
{
foreach (var item in news)
{
drpListNews.Items.Add(item.Title);
}
}
return error:{"The query results cannot be enumerated more than once."}
how can check result != null in LINQ;
View 3 Replies
Dec 1, 2010
The SQL is below - each result is coming up 3 times rather than just once.
SELECT Consignments.LegacyID, TripDate, CollectionName, DeliveryName, Pallets, Weight, BaseRate, Consignments.FuelSurcharge, AdditionalCharges, BaseRate * Quantity AS 'InvoiceValue', Consignments.Customer, InvoiceNumber,
CASE
WHEN child.LegacyID = Consignments.Customer THEN child.LegacyID
WHEN parent.LegacyID = Consignments.Customer THEN parent.LegacyID
ELSE this.LegacyID
END AS 'InvoiceAcc'
FROM SageAccount this
LEFT JOIN SageAccount parent on parent.LegacyID = this.InvoiceAccount
LEFT JOIN SageAccount child on this.LegacyID = child.InvoiceAccount
JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True')
OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True')
OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True')
WHERE (this.LegacyID = @Customer) AND (TripDate BETWEEN @fromdate AND @todate) AND (InvoiceNumber IS NOT NULL)
The SQL was given to me for another similar query, but this time I have modified it to try to use it for the query I'm doing now, so I'm assuming that I'm doing something stupid.
View 2 Replies
Mar 5, 2011
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.
View 3 Replies
Oct 2, 2010
I've managed to use my MS Access database with repeaters and gridviews in my website. Basically, printing the result of a query directly to the website seems easy enough using something like this :
[Code]....
After quite a bit of googling this seems to be a common way to do this. My question then seems kinda silly.
I want to use some of the data from the query in for example a variable without sending the data to a repeater or gridview and so on. Say I just want the username and a password from a database and want to store the username in one variable and the password in another for further use in the code. What do I do?
View 1 Replies
Jan 26, 2011
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.
View 5 Replies
Mar 11, 2011
i query a database for retreiving all the columns. I will probably have many rows. Im trying to output the query results into an html table. This is what i have so far:
[code]....
How could i store the many rows and output them using html as soon as i open a page?
View 3 Replies
Aug 2, 2010
I have an asp.net intranet site hosted on IIS on my computer.
On one page you can enter two user id's and get a side by side comparison of their roles in our (custom app) system.
I often use it to add roles when I'm setting up new users. After running the query, if i change a role and run the query again it will not show updated results. It's being cached somehow. I have to go to another page and come back and them run the query to get updated results.
How can avoid having to navigate away to get the updated query results displayed.n
[Code].....
View 2 Replies
Jan 16, 2011
I have a Query which I run as an SQL. This is my SQL:
"SELECT COUNT(*) As NoDup FROM [XXX Dir] WHERE (FileName Like 'XXX_YYY_20100916.txt')"
The "XXX Dir" is a table and "FileName" is a text field within that table.
When I execute the SQL in the Query development windows, it returns a single value like 2.
I would like to assign that value to a variable so I can use it in my VB code.
View 1 Replies
Jan 19, 2011
i have my own DLL (for the data access layer),,i use escaping characters technique to avoid the user input errors ,, but recently i decided to enhance my classes and using parametrized query to prevent all possible errors,Is the modification will be easy or difficult ??how to convert this way to use the parametrized query ..
View 1 Replies
Jan 26, 2010
I want to display sql query results in my web application (asp.net webpage) dynamically at the run time. But I don't know how to do that. Is there any control to do that? Or is there any code? Could you give some examples?
View 4 Replies
