ADO.NET :: Constructing A Linq Query Using A Collection?
Mar 13, 2011
I'm constructing a query using linq, but I'm having trouble using a collection, i.e. where field in database = anything in the collection.
Below is an example of what I mean:
dim keywords as string ="test,test2,test3,cats,dogs,frogs"
Dim getKeywords = (From cp In myDataContext.pages
Where cp.pageKeywords = currentPageKeywords.Split(",")
The aim of the above code is to return all records where the "pageKeywords" field contains 1st keyword, or 2nd keyword, or 3rd keyword etc.
View 5 Replies
Similar Messages:
Aug 13, 2010
I'm new to LINQ and have a problem with my query not showing me the Table Collection. I manually created my Cart class and specified the table attributes on each property (works fine for inserting records via LINQ see below). DataContext dc = new DataContext(Conn);
var q = (from c in dc.NOTABLES?? where ((c.UserID == userID)) orderby c.CreateDateTime Based on examples I would expect to see dc.Cart But I don't, so I tried to access it via dc.GetTable<Cart>() which at least gets me data but doesn't seem to convert the rows to my Cart object since this:
foreach (var cart in q) Log.Info("UserID: " + cart.UserID + " CartID: " + cart.ID.ToString());
blows up with a "Specified Cast is Not Valid" exception.
No Problem Inserting Also, I had no problem inserting data into the database:
DataContext dc = new DataContext(Conn);
Table<Cart> Carts = dc.GetTable<Cart>();
Cart aCart = new Cart();
aCart.UserID = userId;
aCart.Description = description;
aCart.CreateDateTime = System.DateTime.Now;
aCart.UpdateDateTime = System.DateTime.Now;
Carts.InsertOnSubmit(aCart);
Carts.Context.SubmitChanges();
View 1 Replies
Dec 16, 2010
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")
View 1 Replies
Oct 12, 2010
I need to dynamically construct a url by appending new parameter to the current url.
I know how to do this in the long way by getting the current url and check if there is any QueryString, if no add "?" if yes add "&". Also check is the parameter is already exist. If yes change the value.
But is there any build in .NET function to do this? Like Request.QueryString.Add
View 1 Replies
May 19, 2010
I am trying to implement admin login and operators(india,australia,america) login, now operator id starts with 'IND123' 'AUS123' and 'AM123'
when operator india logs in he can see the details of only members have id 'IND%', the same applies for an australian and american users
and when admin logs in he can see details of members with id 'IND%' or 'AUS%' or 'AM%'
i have a table which defines the role i.e admin or operator and their prefix(IND,AUS respectively)
In loginpage i created a session for Role and prefix
PREFIX = myReader["Prefix"].ToString();
Session["LoginRole"] = myReader["Role"].ToString();
Session["LoginPrefix"] = String.Concat(PREFIX + "%"); works fine
In main page(after login) i have to count the number of member so i wrote
string prefix = Session["LoginPrefix"].ToString();
string role = Session["LoginRole"].ToString();
if (role.Equals("ADMIN"))
StrMemberId = "select count(*) from MemberList";
else
StrMemberId = "select count(*) from MemberList where MemberID like '"+prefix+"'";
Problem: 1. i want to constructor parameter something like
StrMemberId = "select count(*) from MemberList where MemberID like '@prefix+'";
myCommd.Parameters.AddWithValue("@prefix", prefix);
Which is not working
2 Displaying the members in gridview i need to give condition (something like if (role.Equals("ADMIN")) show all members else show member depending on the operator prefix)the list of members in operator mode and admin mode. - where to put the condition in gridview how to apply these
View 2 Replies
May 28, 2010
I'm looking to translate an SQL query into linq
INNER JOIN Usrs ON
SAQ.UserId =
Usrs.UserId AND Scan.UserId =
Users.UserId
I'm not sure how to include the "AND" in the LINQ statement.
View 3 Replies
Aug 12, 2010
Imagine I have 3 classes (student has Marks which has subjects)
[Code]....
I got a List<Student> populated with all the student and nested collection of corresponding Marks and subjects.How do I, before binding this list to any grid, filter records to display only those students, who surely have appeared in subjects with name "X" and "Y"?I tried something like:
[Code]....
View 2 Replies
Feb 11, 2011
I want to use "if statement" in Linq query. How can I do this situation?
For example:
if txtAge.Text=="", I will not use that in Linq Query.
else txtAge.Text!="", I will use that in Linq Query.
View 8 Replies
Jul 28, 2010
is there a way to update a column for all rows in a collection using linq like sql does it
eg. UPDATE myTable SET myColumn='newValue'
Also weighing options as to whether there is better performance using linq to sql for this or straight sql to DB via table adapters etc.
View 2 Replies
Sep 14, 2010
I've build a linq query.But i want a random selection so its not always ID : 1,2,3,4,5,6 How can i randomize this var? I like to bind it to a repeater.//TagCloud:
Random rand = new Random();
var tc1 = from i in JumpTide.cms.menu.GetMenuItems(32)
select new
[code]...
View 1 Replies
Mar 10, 2010
select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 Replies
Nov 23, 2010
here my code-
List<ReservationSlotLimitDetailEntity> sorted = (from p in slotLimitCollection.OfType<ReservationSlotLimitDetailEntity>()
where p.DayOfTheWeek == dayOfTheWeek
select p).ToList<ReservationSlotLimitDetailEntity>();
I want to sort in such a way so that it will add on more temp column "DayName" based on condition such that if dayOfTheWeek==1 DayName=Monday and if dayOfTheWeek==2 DayName=Tuesday and so on.
View 1 Replies
Aug 18, 2010
I have a bunch of news articles and I want to select the most recent one using Linq to Sql. I have an MVC action method like so
[ChildActionOnly]
public ActionResult LatestNews()
{
var article = mhndb.NewsArticles.Single();
return PartialView("LatestNews", article);
}
I would like to know the syntax for selecting the most recent item in the NewsArticles collection using Linq.
View 2 Replies
Apr 5, 2010
I have a partial class extending my Entity Model. I'd like to write a method that returns a collection of types from my entity framework model. For example, I'd like to get all the states to populate a drop down list. So I've written a method called getallstates(), similar to the example below:
[Code]....
I tried returning a list of Objects, and a list of states to my calling method to be used to bind to a drop down list. However I get an error that:
Server Error in Application. Unable to cast object of type 'System.Data.Objects.ObjectQuery to type 'System.Collections.Generic.List`1[System.Object]'.
What should my return type be for this function?
View 2 Replies
Mar 19, 2011
I am facing a big problem with simple linq query.. I am using EF 4.0..
I am trying to take all the records from a table using a linq query:
var result = context.tablename.select(x=>x);
This results in less rows than the normal sql query which is select * from tablename;
This table has more than 5 tables as child objects (foreign key relations: one to one and one to many etc)..
This result variable after executing that linq statement returns records with all child object values without doing a include statement.. I don't know is it a default behavior of EF 4.0 . I tried this statement in linqpad also..but there is no use... But interesting thing is if I do a join on the same table with another one table is working same is sql inner join and count is same..but I don't know why is it acting differently with that table only.. Is it doing inner joins with all child tables before returning the all records of that parent table?
View 2 Replies
Oct 17, 2010
I am writing a linq query to select a blogpost,
[code]....
The blogpost i am testing got 3 tags attached to it. The table structure is:
(table)BlogPost -> (table)BlogPostTags <- (table)Tags
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.
View 1 Replies
Feb 22, 2011
I have a dynamic gridview which can be used to add new rows of two columns to specify a start date and an end date. I am trying to use a string collection to pass in the output from the textboxes to a parameterized query as shows. I am getting an incorrect syntax near 9 error. How can I make this work? I need to use the stringbuilder.
protected void btnSaveIterations_Click(object sender, EventArgs e)
{
int rowIndex = 0;
StringCollection sc = new StringCollection();
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("start_iteration");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("end_iteration");
start_date = box1.Text;
end_date = box2.Text;...............
View 3 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
Sep 6, 2010
I need to implement a search page where a user will be able to specify some search criteria and perform a search. It's a requirement that the results are displayed on the same page, beneath the search criteria.
Here's the code for the controller class:
[Code]....
For simplicity, the table string above is hard coded but would be constructed dynamically in the real code.
I also have the following form defined In the body of my html:
[Code]....
Finally, I'm using JQuery to hijack the submission of the form and to request the html for the table:
<script type="text/javascript" language="javascript">
$(document).ready(function() {
$("form[action$='GetTableData']").submit(function() {
$.post($(this).attr("action"), $(this).serialize(), function(response) {
$("#tablePanel").html(response);
});
return false;
});
})
</script>
I'm fairly new to ASP.NET and MVC but the thing that slightly bothers me is that the html is generated in the code-behind for the view.
View 3 Replies
Jan 19, 2011
I am wondering if you might be able to with a little problem.I am trying to learn more about building web services by constructing one but I have run into what I think is a syntax problem that I can't figure out. Below is my C# code:
[Code]....
View 7 Replies
Mar 10, 2010
I have to set GridView.DataKeyNames(This is really a question about the syntax for a string array, in VB.Net.)
I am discovering the PrimaryKey columns names from within a loop, ie:
For Each fld As IEntityField In Me.llbDataSource.EntityCollection(0).PrimaryKeyFields 'fld.Name ' this is where the column name is stored, so I have to get this into an array Next I can not use this syntax.
GridView.DataKeyNames = new string() { "PowRatHP", "Voltage", "Phases", "Poles", "Drive" }
So, basically, how does one declare a string array (as required by GridView.DataKeyNames), and then populate the elements of the array from within a loop?
View 3 Replies
Sep 14, 2010
how to query return all data in Array Object ,Hash Table ,And DataTable dataRow
View 4 Replies
Aug 2, 2010
I have a list which contains few email ids
List<string> EmailId= { ....}
Now I have a db table Users. I need to select all users whose email exist in the above list. In sql we could write "where emailid in ('email1', email2' ,...)"
how to do this in linq to sqlquerable<Users> existingUsers = Users.getTable().where (u=>u.emailaddress in EmailId). I want to do some thing similar
View 2 Replies
Sep 12, 2010
I need to figure out how to express this in linq to sql:I have one or more records that contain what is essentially a wildcard sql parameter, like '100_-___-2', which should match 100[any 1 char]-[any 1 char][any 1 char][any 1 char]-2, for example. Each login will have one or more of these 'datamasks' associated with it.I need to write a query in linq to sql that does something like:
select * from something
where fieldA = 'someValue' or fieldB = 'someValue'
so far, simple, but I also need to restrict the returned records to only those that match the user's 'datamasks'. I'm not sure exactly how to translate this to linq to sql... here is what a working query *without* the datamasks part looks like:
[Code]....
I need to tack on the datamasks part... can I do it right there in the same statement? or do I need to do a foreach loop on the datamasks records and append a new && condition for each one? hmmm... just thought of that as I was writing this.. I'll try that out. But I'm posting this anyway for more input... I guess that would look something like:
foreach (string mask in datamasks)
{
query = query.Where(item =>
System.Data.Linq.SqlClient.SqlMethods.Like(item.AccountNum, mask));
}
would that work? would each additional 'where' condition be 'appended' to the existing query definition?
View 3 Replies
Mar 11, 2010
Users
Roles
UserRoles
MenuItems
RoleMenuItems
A User can have multiple Roles and a MenuItem can be accessed by multiple Roles. Now I want to write a method as follows:
public IList<MenuItems> GetMenuItems(UserRoles userRoles)
{
var menus = // LINQ query to get the MenuItems by UserRoles
return menus.ToList();
View 1 Replies