I have a couple tables that are kind of unrelated - id like to search through both of them and create a type that i can sift through later
something like this doesnt work
[Code]....
I basically want to create a list of "AnimalSearchResults" that contains all dogs and all cats that have that name Whats the best way to do something like this?
We have a database with a lot of information about Persons. I won't post the entire database structure because it is too big, but it looks something like this:
Person
ID
Name
Street
City
State
Country
Language
LangCode
Language
Interest
ID
LastChangedBy
LastChangedOn
LocalizedInterest
InterestID
LangCode
Description
PersonInterest
PersonID
InterestID
Now, this is just a small example. In our database, we have about 8-9 localized tables (like Interest) a Person can be linked to. A Person can have multiple Interests, a Person can have multiple Jobs, a Person can have multiple Educations, a Person can have multiple Experiences, I have to build a search function. Let's say you enter "tom" as the search term. This should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables.
If you enter multiple words to search for (eg. "tom php"), it should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables AND "php" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables. At the moment there are about 4,500 records in the Person table and if I do an outer join of Person with all the tables I have to search, there are about 1,300,000 records and 40-50 fields to search in.
How should I approach this problem so that performance will be acceptable? The client expects "something like Google" in terms of speed and ease of use. We're using MS SQL Server 2000 and ASP.NET 2.0. The search functionality has to be added to an existing application and changing the technology or database structure is not an option.
I have a partitioned db where several tables have the exact same structure. I'm able to query the tables using linq, but only by quering the individual tables 1 at a time and by using the explicit table name. For example at the moment, I have 8 tables so I have something similar to the following:
[Code]....
Is there any way, that based on a certain value (stringValue), I could determine which table to query and then code the linq query just once?
Using Linq to Sql how do i group the following 2 tables.
Orders Table:
CustomerID | Name |Date
1 | order1 | 2010-01-01
2 | order2 | 2010-01-01
2 | order3 | 2010-04-01
Calls Table:
CustomerID | Name |Date
1 | call1 | 2010-01-01
3 | call2 | 2010-06-01
2 | call3 | 2010-05-01
I want to group the two tables by date , Result:
Date | Orders | Calls
2010-01-01 | 2 | 1
2010-04-01 | 1 | 0
2010-05-01 | 0 | 1
2010-06-01 | 0 | 1
i know how to group a single table ,from o in Orders group o by o.Date.Date into og select new {Date = og.Key,Orders= og.Count()};
Does anyone have some really great tutorials on how to use link with datasets that contain multiple tables that utilise primary key and foreign key relations? For eample
Table1
- id (PK)
- language
Table2
-id (PK)
-programmingword
Table3
-id (PK)
-languages_ID (FK)
-pgrammingword_ID (FK)
-translatedword
Linq search for: Table3.translatedword where Table1.Language ='french' and Table2.programmingword = "yes" does this make sense? im looking for exampes/ tutorials for how to do this? or can anyone recommend any good books on linq?
I'm a newbie to this so bear with me. From what I've read online LINQ to Entity should see the relationships without having to specify the joins between multiple tables. I have a relationship that goes across 4 tables Employee, EmployeeDepartmentLink, EmployeeProjectLink, and Project. The idea is an employee can be in multiple departments and work on multiple projects with it being specified which department the employee is in for that project.
This is a C# MVC3 program connecting to a SQL database using VS 2010 Express.
I have no problem with running this query to find employees on a project:
[Code]....
Tring to do the reverse, finding projects that an employee worked on results in an error:
[Code]....
Error message: The specified type member 'EmployeeDepartmentLinks' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. So I have 2 questions,
1) Why would it work in one direction and not the other? I've played around with it for a couple days with the same results.
2) Does it matter that my primary key and foreign keys are not named the same since all the relationships are connected in SQL? Ex: in Employee (EmployeePK)--(EmployeeFK) in EmployeeDepartmentLink Trying different stuff in LINQPad I was able to get the 2nd query to work if I specified all the joins which I thought was not neccesary with LINQ to Entity. I converted an SQL query that I made on the server into a LINQ query below which works.
I'm using ASP.NET MVC to create a small web apllication.
Here is my database And here is my class
[Code]....
Is it possible to create a table class that can be used for multiple tables? For example, if I have some class:
[Code]....
In LINQ to SQL, this maps to a table with a name based on the class name, DBClass. However, suppose I want to use that class for multiple tables, and it could be an arbitrary number of tables. Is this possible
how can I display a linq query in a gridview that has data from multiple tables AND allow the user to edit some of the fields or delete the data from a single table?
I'd like to do this with either a linqdatasource or a linq query. I'm aware I can set the e.Result to the query on the selecting event. I've also been able to build a custom databound control for displaying the linq relations (parent.child).
However, I'm not sure how I can make this work with delete? I'm thinking I may need to handle the delete event with custom code.
I have two tables with a foreign key constraint how can I delete rows from both of them in one transaction? linq to SQL seems to call my deletes in the wrong order. Is there somewhere I can check and make sure the constraint is recognized properly by linq to SQL??
DataContext.OtherImages.DeleteOnSubmit(myOtherImage);
DataContext.Images.DeleteOnSubmit(myImage);
DataContext.SubmitChanges();
The Foreign key constraint is on OtherImages.
I'm looking for ideas on deleting data from the multiple SQL tables with LINQ (C#).
My existing solution does the following:
1. DeleteAllOnSubmit( Subset of the data from the child table)
2. DeleteAllOnSubmit( Subset of the data from another child table)
3. DeleteAllOnSubmit( Data from the parent table)
4. CommitChanges()
All this resides within a single method, which makes it ugly and not re-usable.
I have considered the following alternatives, but I decided not to use them:
1. Cascade delete on the tables.
2. Do a join on all tables from which I will remove the data, but I'm not sure yet whether I could pass a join query to DeleteAllOnSubmit().
3. Write stored procedure(s) to delete data from each table. I don't have the time to implement this due to a large number of tables and the time constraints.
I'm having trouble doing a left outer join with multiple generic lists tables. I've been researching for hours and there isn't a whole lot out there on this subject and VB (at least what I found).
I have the following Linq query:
Dim DataForGridview = (From s In S1 _
Join c In C1 On c.ID Equals s.ID _
Join v In V1 On v.ID Equals s.ID _
Select New With { _
.dataFieldID = s.ID, _
.dataField1 = s.dataField1, _
.dataField2 = c.dataField2, _
.dataField3 = c.dataField3, _
.dataField4 = c.dataField4, _
.dataField5 = v.dataField5 _
}).ToList()
S1 has a count of 30. V1 has a count of 20. C1 has a count of 10. This query joins the three but ends up with a count of only 10 based on the datakey ID and, of course, only shows those 10 in the gridview.
I would like to show all 30 from S1 in the gridview with blank fields wherever V1 and C1 do not have data. From what I've read I need to do a left outer join. However, all examples I've found are with two datasets.
I've tried multiple times expanding on what I've found by changing it to a group join and also adding the .DefaultIfEmpty() property in the query, but nothing is working.
How can I change the above query to a left outer join with these multiple tables?
I have one gridview, a dropdown list where I can search the users table or a group name table, a textbox for inputting the search and a button to search.How could I overwrite the users table that is binded in the gridview when I search through the groupname table? Using C#.
View 2 RepliesI'm trying to write a LINQ to SQL for searching my database. What's the best method or LINQ to SQL comparing condition?
View 2 RepliesI have to display records from two tables where foreign key table contains many values for a primary key column how to get the data using linq query???
View 1 RepliesFirst off I'm a week into asp so apologies in advance for silly questions! I am looking to connect to a SQL database table and allow the user to filter the table based on a search box on the asp page. I have written a sql query with a parameter in the where clause, a bit like this:
select col1, col2
from table
where col1 like @my_parameter
Then I pointed the parameter to a textbox on my asp page, and this seems to work quite well. However, I would now like to add an additional search facility which would be a dropdown list. This would be sourcing all the unique values from the col2 in my table abve (there are only about 10 possible values here). But I don't want users to be able to use both of these searches at the same time.
So the bottom line is I would like to offer two methods of filtering the same data table, which search on different columns of the data table. Then the results should populate the same results table, but the user must only be able to use one search method or the other.
I have done a bit of research but couldn't find anything along these lines (I think it is becuase I don't know the words of the things I should be searching for!). I did find information on a standard postback event or cross page posting - not sure which of these (if any) is the best to use for a beginner?
I have an C# ASP application I am writing that needs to have the capability to import a generated excel or a comma delineated sheet each day. A clerk will have this job each morning so it doesn't need to be automated. My problem in trying to understand the solution to this is that the 1 sheet contains loan information, including customer information all in the same sheet. I would like to send certain columns to update information in the loan table and send other information to update the customer table. I need it to create relationships when new loans appear in the spreadsheet.
View 1 RepliesI want to query an SQL table using some Web controls to provide the parameters for filtering the records in the table. Results are displayed in a GridView.
I have a TextBox with an ID of "keyword" where the user may enter one or more keywords. I'd like to search 2 columns for instances of these keywords.
I also have a DropDownList named "category" that lists categories contained in a "category" column of the table.
I have a dataset with a TableAdapter for the table I want to search. Got it working fine with the DropDown List but not sure how to proceed with the TextBox and keywords.
What SQL query should I use? The user may not enter anything into the textbox, they may enter one word, or multiple words.
This is where I am now:
SELECT * FROM tablename WHERE category = @category AND ??
I am trying to create a form in MVC C# that will allow a user to input a Last Name, First Name, Department, Year and click a Search button that will bring back a list of employees based off the inputted search criteria.My problem is allowing multiple search textbox criteria into one search button. I am able to hardcode values into an html actionlink like below and it works but unable to grab the values from the textboxes.
<%= Html.ActionLink("Results", "Results", new { lastName = "Smith",
firstName = "", dept = "", year = "2008" } )%>
I would really just like to have four textboxes and a search button to bring the list back from the database.
I have three text boxs
1.txtname
2.txtage
3.txtmobile
and one dropdownlist
1.ddlcity
for search client information.
My requirement is when search button click the client information will bind in gridview based on data filled in textbox and dropdown list.
The difficulty is user can enter any combination for search. E.g.
1.age only
2.name and city
3.city only etc.
I want to select data from a table where the user can search by 3 options; date, keyword, and type. The date will always be either today or a user chosen date. The keyword can be null or something the user inputs. The type will be chosen from a drop down and can be all or a specific category. I know how to search the date, but the other two create complications. I have created a stored procedure trying to figure this out:
[Code]....
Is there a way to say if the keyword is null then don't use it as a search criteria. If that is possible then type can use that same logic I suppose.
I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets. I found a very good article in [URL].[URL] was able to successfully export each of my datatables in the dataset to excel worksheets. This solution worked for small number of rows. But when the data got larger I am consistently getting the system.outofmemoryexception. THis is because the code is using up the memory when creating excel.
Does anyone have another option to do the above?
I created 3 product table . A,B and c.
I show product of table in different gridview.
Now I am using one search box. but how to search product name with image in one query all of three table....
Simple how to search product from multiple table and show result...
I need to make a category filter. For this purpose I have two tables "Ingredient" and "Category"
My Category Table looks like this:
[Code]....
And my ingredient table:
[Code]....
I want to join the two tables so when the KategoriID match it should then return all from that category.
[Code]....
[Code]....
I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:
SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17';
can be expressed as:
var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 };
But how would I join 3 or more tables using LINQ? For example:
SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37';
I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement.
