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
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?
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?
public class Category { public int CategoryId { get; set; } public string Name { get; set; } } public partial class CategoryMap : EntityTypeConfiguration<Category> { public CategoryMap() { this.HasKey(c => c.CategoryId); this.Property(c => c.Name).IsRequired().HasMaxLength(400); } } public class MyObjectContext : DbContext { public MyObjectContext(string connectionStringName) : base(connectionStringName) { } public DbSet<Category> Categories { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new CategoryMap()); base.OnModelCreating(modelBuilder); } }
Now when I run the following code I get an exception (GenericArguments[0], 'System.Int32', on 'System.Data.Entity.Internal.Linq.ReplacementDbQueryWrapper`1[TEntity]' violates the constraint of type 'TEntity')
DbDatabase.SetInitializer<MyObjectContext>(new DropCreateDatabaseIfModelChanges<MyObjectContext>()); using (var context = new MyObjectContext("NopSqlConnection")) { var query1 = from c in context.Categories select c.CategoryId; var test1 = query1.ToList(); //works fine var query2 = from c in context.Categories where query1.Contains(c.CategoryId) orderby c.Name descending select c; var test2 = query2.ToList(); //throws the exception }
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.
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?
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?
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?
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.
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.
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.
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.
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.
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'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?
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 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?
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
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 ..
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?
I need to concatenate several columns of a table into a single value, then show that value in an asp dropdownlist. The SQL code I'm issuing is as follows:
SELECT UserID, CustomerNum, UserName + ' - ' + UserAddress + ',' + UserCity + ' ' + UserState AS UserInfo FROM Users WHERE (CustomerNum = @CustomerNum) ORDER BY UserName
I then set 'UserInfo' as the text field in the dropdownlist.This generally works, except occasionally one of the columns in the database is null (for example, UserState). When that happens, the entire concatenation is null, and I get an empty entry in the dropdownlist.
Is there something in SQLServer that will allow me to ignore those NULL results, or will I have to code something up in the DataBind event?
I'm using Visual Web Developer 2010 Express. This is my first attempt at creating a .NET page. I have a Gridview on a page that displays the results of a GridQuery. This works. I have a checkbox control(cb_Filter) on the same page that if checked, should add to the where clause of the GridQuery, (where Column5 IS NULL). How do I check the state of the check box so it will run the query with or without the "filter".
