i use Default membership provider for my membership
i add a profile table for some more information from user and it related with UserId with aspnet_Users
to get data from my database i use Entity Data Model
now i want to write a Linq to get data from all 3 tables
I'm trying to build a linq2Entity/gridview function for a forum, that draws data from multible tables.The result should be: Get the latest 10 threads that user(xx) has replyed in, that is not disabled or has a subject that is not allowed.Include information on the last post, thread owner user, and last post user, in each thread.
ere is my code... is returns the correct threads and some that should not be there.
[Code]....
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.
I have T_articles to save the articles.
each article has authorCode , and categoryCode.
each article can be modified by the author several times.
and I save every modification in the T_articles in a difference row.
now I want to dislpay for each autor:
author name, article name , article category's name, last modified date
In SQL I should to it like that:
[Code]....
I try to write it in Linq so:
[Code]....
so I got error:
[Code]....
and again I got an error:
[Code]....
Here's my DB structure:
I have three tables.
User, Details, Optional
User has fields: ID, FirstName, LastName
Details has fields: ID, MyDetails1, MyDetails2, User_ID
Optional has fields: ID, MyOptions1, MyOptions2, User_ID
There is a 1 to many relationship with User & Detail (User ID & Detail User_ID)
There is a 1 to many relationship with User & Optional (User ID & Optional User_ID)
With that said, one user can have many details and/or many optionals
Now I'm trying to build a linq query that will say:
"where user id = x, select FirstName, LastName, all MyDetails1 from every record where User_ID == x, all MyOptions1 from every record where User_ID == x"
After the merge I should have one string that lists the distict user & all of their many fields:
FirstName + LastName + (MyDetails1 + MyDetails1 + MyDetails1..etc) + (MyOptions1 + MyOptions1)
Anyone try this before?
I tried this with JOIN but this doesn't do the trick for what I'm looking for.
So if I had 3 MyDetails records associated with ID == 2, I would get three results like:
John Doe MyDetails_X
John Doe MyDetails_Y
John Doe MyDetails_Z
I am trying to put a Right Outer Join on Linq statements. how to put Right Outer Join on Linq.
View 1 RepliesI am new to LINQ. I have a GridView which I am populating using LINQ. My LINQ statement is taking query string from previous page. The query string is in string format. Here is the code:
protected void Page_Load(object sender, EventArgs e)
{
string getEntity = Request.QueryString["EntityID"];
[code]...
The main problem is that I recieve the following message:
"base {System.SystemException} = {"Unable to create a constant value of type 'BokButik1.Models.Book-Author'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}"
based on this LinQ code:
[Code]....
How shall I solve this problem to get a class instance that contain with property title and Book-AuthorID?I also have tried making some dummy by using "allbooks" relation with Code Samples from the address
http://www.hookedonlinq.com/JoinOperator.ashx. Unfortunately, still same problem.
I also have taken account to Int32 due to entity framework http://msdn.microsoft.com/en-us/library/bb896317.aspx. Unfortunatley, still same problem.
Using database with 3 tables and one of them is a many to many relationship. This database is used in relation with entity framework
Book-Author
Book-Author (int)
BookID (int)[code]....
private void GetResults()
Use an Expression on a Linq Outer Join
I ned to run a query with two joins, the second join does not work please help with the correct syntax.
[Code]....
I have the below query which has left join and count in SQL. Please help me convert this SQL query to LINQ (using Entity frameowrk).
[Code]....
I have this code:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
[code]...
I have this group-join syntax but I couldn't get the distinct value from the relational tables below.
Table_1
key_1 t1_value
1 Food
2 Sports
3 Leisure
4 Trip
5 Zoo
Table_2
key_2 key_1 t2_boolean
15 1 True
16 1 True
17 1 True
18 2 True
19 2 True
20 2 False
desire result:
[code]...
I am working on a website similar to nerddinner. Is it possible to perform similar join
using Linq to entities what is done with linq to sql in nerddinner.I am posting the codes below.
public IQueryable<Dinner> FindByLocation(float latitude, float longitude) {
var dinners = from dinner in FindUpcomingDinners()
join i in db.NearestDinners(latitude, longitude)
[code]...
I have the following LINQ query that does a left join:
var results = from v in ctx.dat_Visitor
join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sp2
select new {
[code]...
My problem is that this LINQ query times out on me. I even upped the default timeout from 15 seconds to 120 seconds and it still times out. dat_Visitor has no more than 10 records in it, but vwSP (which is a view) has somewhere around 100,000 records. I know for sure that the timeout has to do with when I attempt to actually return sp2 in the results. If I change my LINQ query to not return sp2 in the results, then it is quick. Without sp2 the query looks like such:
var results = from v in ctx.dat_Visitor
join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sp2
select new {
Visitor = v.Name,
Tot = sp2.Count()
};
Lastly, I'm using the 3.5 framework, but I am having serious thoughts about upgrading the website to 4.0, since I could then use DefaultIfEmpty().
I am having trouble with duplicate records. I tested .distinct() in several areas and nothing helps.
I got the search criteria from the PreviousPage. I then used this criteria to search QueryA.
I did a search using QueryA. I took the results, and put the IDs into an array.
I then did a search using QueryB, that matched the IDs in the array. QueryB is used for the ListView and to page records.
Using test records. QueryA results = 2 records. The array.length = 2. QueryB results = 3. ?
What am I doing wrong ?
Some of my Code:
myResults = (from r in QueryA
select r.ID).Distinct().ToArray();
totalrecords = myResults.Length; // = 2
using (ToHealthEN n = new ToHealthEN())
{
[Code]...
We have two related tables (inventory, inventoryLocalization) and a t-sql query works on them as follows..
select inv.[code],
coalesce(inL.name, inL2.name) as [name],
coalesce(inL.description, inL2.description) as [description]
from dbo.[inventory] inv
left join dbo.[inventoryLocalization] inL on inv.code = inL.code and inL.language = 'de'
left join dbo.[inventoryLocalization] inL2 on inv.code = inL2.code and inL2.language = 'en'
where inv.[code] like '15.' + '%'
order by inv.[code];
to performance improvement, we will try to use these tables from cache not from RDBMS directly.
for an asp.net 4.0 project, by c#. We've took both tables to chache with a DataSet and we can reach both of them through that DataSet.
DataSet ds = UtilityCache.getCachedDataSet();
"ds.Tables[0]" is inventory Table and "ds.Tables[1]" is inventoryLocalization Table
But how can we convert above T-Sql syntax to linq2Sql. is it possible to use coalesce for select statement and to join on two tables with multiple conditions ?
I have a question about Entity Framework. answer if you know answer on this. I have such query :
[Code]....
Particularly I want to join few tables in one query, but I can NOT use LINQ and can NOT use ObjectQuery with objects mapped to DB fields inside my query. Because each entity creates dynamically. So this is what i
can NOT use : [URL]
The question is can I use something like this instead of using objects?
[Code]....
The purpose is to use Join method of ObjectQuery with syntax as in Where method :[URL]
How to use Left and Right Outer Join in C# using Linq with Eg.
View 2 Repliesi am trying to use predicatebuilder for a linq to sql query but i cant seem to get a join to another table. in my model, i have a table for applicants and another for job applications.. each applicant can have several job applications.
i expect in linq to sql to be able to do something like;
p.jobapptable.jobappdate where p is the applicant.
this isnt the case..i am trying to use predicatebuilder to search through applicants based on the job applications they have made? how`can i get linq to sql to show the foreign key relationships?
I have two tables namely "CategoryMapping" and "PartnerCategory"...The partnercategory table has foreign key with categorymapping on CategoryID...
let us say categorymapping should be filed first then partnercategory should be filed based on the categories defined in categorymapping. For example my two tables have the following data:
[code]....
I want to get data from Projects(which have CourseId) and related CourseName from Courses table.
I have written following code:
var projects = from n in db.Projects
join c in db.Courses on n.CourseId equals c.ID
orderby n.Name
select new { Project = n, Course = c };
return View(projects.ToList());
and I get error:
The model item passed into the dictionary is of type 'System.Collections.Generic.List1[<>f__AnonymousType22[ProjectManager.Models.Project,ProjectManager.Models.Course]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[ProjectManager.Models.Project]'.
What I need to do in Controller and in View to display this data?
I have this code in my controller:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
Guid UserId = (Guid)currentUser.ProviderUserKey;
using (var db = new MatchGamingEntities())
{
var MyAccount = from m in db.Accounts..........
I want to be able to do a join query between my two tables Accounts and BankTransactions. This is a one to many relationship, there can be multiple BankTransactions per Account. I want to query this and display the account information including all the bank statements that are associated with it. I did a join in order to get it, but I am having trouble handling the model. I keep getting this error:
LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[MatchGaming.Models.BankTransaction] ToList[BankTransaction](System.Collections.Generic.IEnumerable1[MatchGaming.Models.BankTransaction])' method, and this method cannot be translated into a store expression.
I'm trying to achieve an outer join in LINQ but I'm not getting my desired result.There are two relational tables between Fruit_Name and Fruit_Ripe.The Fruit_Ripe table has a boolean field (IsRipe) that I use in my condition.The condition let me show all fruits that is not yet rippen (false) or fruits that isn't inputted yet in Fruit_Ripe table.
Dim fruit_salad = From myFruit In _DataContext.Fruit_Name
Group Join myRipeFruit In _DataContext.Fruit_Ripe On myFruit.fId Equals myRipeFruit.fId Into Group
From fruitty In Group.Where(Function(d) Not d.isRipe =
[code]...