ADO.NET :: Can't Combine "LINQ Join" With Other Tables?

Jan 8, 2011

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]....

View 1 Replies


Similar Messages:

ADO.NET :: Join Two Tables Using LINQ?

Jan 24, 2011

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]....

View 2 Replies

ADO.NET :: LINQ To SQL - How Do You Join More Than 2 Tables

Sep 9, 2010

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.

View 4 Replies

ADO.NET :: Can't Group With Linq When Join Several Tables

Dec 9, 2010

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]....

View 4 Replies

ADO.NET :: How To Join 3 Related Tables With Linq To Sql

Nov 9, 2010

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

View 1 Replies

ADO.NET :: Entity Framework - How To Join Tables Without LINQ And With Only String

Sep 30, 2010

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]

View 2 Replies

DataSource Controls :: How To Join Two Tables And Get Uncommon Records Using LINQ

Jan 22, 2010

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]....

View 3 Replies

Get Data From Two Tables (join) With Linq And Return Result Into View?

Mar 4, 2011

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?

View 2 Replies

ADO.NET :: Left Outer Join With Multiple Tables And Generic Lists In Linq And VB?

Nov 16, 2010

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?

View 11 Replies

ADO.NET :: Forum Function Using Linq, Group, Distinct Left Join, Join?

Mar 24, 2011

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]....

View 1 Replies

ADO.NET :: Join 3 Tables Using Inner Join?

Sep 8, 2010

i need join 3 tables using inner join...

View 6 Replies

DataSource Controls :: Combine Data From 2 Tables

Mar 27, 2010

I have 2 tables with UserId and FirstName, LastName Columns that I want to combine the data in a Select Statement or Stored Procedure

1. The 2 tables will never have the same data, each table will be distinct

2. I want to combine first and lastnames to a full name (t1.FirstName + ' ' + t1.LastName) AS FullName

3. The statement will be used to populate a dropdownlist with UserId as Value, and Name as text

I am unsure how to accomplish this since I do not need to connect the tables, but combine the Sum of their data.

View 6 Replies

DataSource Controls :: Statement To Combine Columns Of Different Tables

Feb 4, 2010

I have 2 tables and I want to entries of the tables into something like this:

Table 1: Table 2: End Result:
a 1 a - 1
b 2 a - 2
... ... b - 1
... ... b - 2
... ... ...

View 3 Replies

Combine Data From Multiple Tables Into One View Model

Dec 5, 2010

I'm fairly new to all this, but have slowly been teaching myself C# and ASP.net MVC over the past year or so...I really tried to search for this, but haven't been able to find anything - it may be I'm using the wrong terminology!I'm using ASP.NET MVC2 with EF4 connected to a SQL Express db. Situation: Without getting into too much specifics, I have one table called 'Plan.' Under 'Plan' are several different categories that are related to a single Plan item [say Category A, Category B, Category C and Category D]. Each 'Category X' table has a beginDate, endDate and other details specific to that category.

I'd like to be able to create a summary page that combines all items from Category A, Category B, Category C, and Category D and sorts by beginDate - but only need a few of the columns [eg - begin/end date, name, id].

View 1 Replies

2 Join Tables Eval?

Jun 23, 2010

if you have two table inner join like SELECT* From A INNER JOIN B on A.ID = B.IDSay A and B tables both have Username values but A's username is Test, and B's username is Test2When I do Eval("Username") it always print out "Test", how can I configure it to print out "Test2"... meaning how to specify which table's value to put using eval.. is there something like Eval(B.("Username"))??

View 6 Replies

ADO.NET :: How To Join The Two Tables With A Constraint

Nov 23, 2010

I have a scenario, I will pass a StatusID , According to that StatusID , The rows will be fetched from a table[tblEventStatus] and newly fetched row has to fecth the data from another table [tblEvent].

Ex: StatusID - Value I am Passing

Table 1: tblEventStatus - Column:SysID - StatusId - EventID
Table 2: tblEvent - Column:SysID - EventID - EventDesc

I want to use the Linq - I tried like this, But It doesnt work
var query1 = from objtblEventStatus in db.tblEventStatus

where objtblEventStatus.StatusId== StatusId
join objev_events in db.tblEvent on objtblEventStatus.EventID equals
(objev_events == null ? null : objev_events.UID)
select new { Category = objev_events.EventID , Name = objev_events.EventDesc};

View 1 Replies

SQL Server :: Cross Join Two Tables?

Sep 10, 2010

TABLE 1
SELECT I.STUNA
,S.SNO
,SUM(B.AMT * Y.YEAR) AS AMT
FROM STUDENT S
LEFT JOIN INFO I ON S.SNO = I.SNO
LEFT JOIN BONUS B ON S.SNO = B.SSNO
LEFT JOIN YEAR Y ON S.SNO = Y.SSNO
STUNA SNO AMT
JOHN A 10
LISA B 20
ALLEN C 100

TABLE 2
SNO AMT
A 1
B 2
C 3
D 5

I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)

STUNA SNO AMT
JOHN A 11
LISA B 22
ALLEN C 103
EDDIE D 5

I TRIED USE THIS QEURY

SELECT I.STUNA
,S.SNO
,SUM(B.AMT) AS AMT
FROM STUDENT S
LEFT JOIN INFO I ON S.SNO = I.SNO
LEFT JOIN BONUS B ON S.SNO = B.SSNO
LEFT JOIN
(
SELECT SNO, SUM(AMT) AS AMT
FROM TABLE2
GROUP BY SNO
) T2 ON S.SNO = T2.SNO
GROUP BY I.STUNA,S.SNO
THE RESULT IS
STUNA SNO AMT
JOHN A 11
LISA B 22
ALLEN C 103

THE "EDDIE" WAS MISS. ALSO I TRY USE CROSS JOIN... THE RESULT IS STILL NOT CORRECT.

View 6 Replies

SQL Server :: Join Two Tables In Different Databases?

Sep 22, 2010

i need to join two tables in different databases, how it can be done??? and if i want to use sqldatasource control to bind results to some data-bound control how can I configure my sqldatasource control???

View 3 Replies

MVC :: Join 2 SQL Tables Together And Return Them In A Json?

Aug 3, 2010

I am new to MVC and am trying to join 2 SQL tables together and return them in a Json. One table is OutreachProgram. The other table is UserInfo. I want to return the rows from the OutreachProgram table that match on CenterID with the UserInfo table.

The code that exists now will pull all rows from the OutreachProgram table. I can't figure out how to add the UserInfo table to the query to make it work.

JsonGrid <OutreachProgram> grid =
new
JsonGrid<OutreachProgram>(db.OutreachProgram,
new
OutreachProgramFilter());
public Array GetItems(IQueryable<OutreachProgram>
source)
{ return (from r
in source.AsEnumerable()
select
new {
ID = r.OutreachProgramID, CenterID = r.CenterID, DateOutreachProvided = r.DateOutreachProvided.ToShortDateString(),
NameOfProvider = r.NameofProvider.ToString(),
}).ToArray();
}

View 2 Replies

SQL Server :: Get All Data From Tables When Inner Join Is Not Enough?

Sep 28, 2010

Suppose I have two tables "t1" and "t2"

t1 :
id FirstName Age
1 Joe 22
2 James 33
3 Bart 28
t2:
id LastName
1 Coymer
2 Manes

I can combine the data from the two tables using an inner join.

1 Joe 22 CoyLap
2 James 33 Manes

The problem though is that the person "Bart" with an Id of 3 is left out because he does not have an entry in the second table.So how can I include Bart in the output in such a way that the output might look like this:

1 Joe 22 CoyLap
2 James 33 Manes
3 Bart null null

Or spaces can instead appear for Barts last name and age instead of null.Basically I just need Bart in the output as well even though he has no age and last name.

View 3 Replies

ADO.NET :: Combine 2 Linq Queries?

Jul 27, 2010

Is it possible to combine these 2 linq queries ?

[Code]....

View 2 Replies

DataSource Controls :: Columns To Join Tables?

Apr 27, 2010

I need to join some tables to get the matching records. I often have to ask around to see how certain tables could be joined. Is there any way to query the information schema or some thing else to see what columns/values match in certain tables in order to figure out how tables should be joined.

View 4 Replies

SQL Reporting :: Join Two Tables And Prepare Report?

Aug 23, 2010

I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.

How do I join those two tables and get that field value in the existing select query.?

View 5 Replies

SQL Server :: How To Join 2 Tables To Get Specific Results

Jan 13, 2011

I have 2 tables:

Attributes
AtrributeVales
attrId(PK) name
id (PK) attrId(FK) value
1
color 1
1 green
2
size 2
2 small
3 1 red
4 2 medium

I need to build some query that will return something like this:

Color Size
Green small
Red medium

View 17 Replies

ADO.NET :: JOIN Tables From Different Physical Database Servers?

Jan 11, 2011

Basically I want to JOIN a table from a MySQL database to a table from a MSSQL database. The reason is because I don't own the MySQL database and I only have SELECT privileges to it. For my ASP.NET application, I need to create two new tables. I have my own MSSQL DB and I have an SA account for it. Is this possible?
This is how I connect to MySQL:

[Code]....

I think the problem here is that I need 2 open database connections for the DataAdapter to use?

View 2 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved