ADO.NET :: Ef 4.0 Cross Database Joins?
Mar 10, 2011does anybody knows if EF 4.0 supports cross database joins for its entities?
View 1 Repliesdoes anybody knows if EF 4.0 supports cross database joins for its entities?
View 1 RepliesI have a requirement where i need to combine tables from two different datacontext. Does Cross context is allowed in Linq??
i wrote a linq query but its throwing error.
Now I'm developing web application with ASP.Net MVC but I have to use 3 databases. So I have no idea about this. I have to extract the data from 2 databases and I have to save the data to another 3rd database.
View 7 RepliesIs it possible to do an optional join as follows?:
private void OptionalJoin(string phrase)
{
from t in db.Table
// (if phrase != string.Empty then) join t2 in db.Table2 on t.Id equals t2.Id
select t
}
Although I'm aware that I can copy/paste this and move the conditional logic outside the query, the query is ~200 lines and there are 4 different optional joins I would like to incorporate, so it would get messy and be difficult to maintain. I'm looking for a clean, streamlined way to do this.
This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID.I know it is a problem with Joins. Could anyone tell me what is wrong with my Query,
Here is my query
select ContactTracking.Contact_ID
FROM
ContactTracking ContactTracking
LEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_ID
LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID
INNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_User
INNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserType
WHERE
ContactTracking.contact_date>'10-jan-2009'
AND ContactTracking.Call_end_time is not null
AND Contact_ID in ('122734','122738')
And here is the ContactId result i get,
122734
122734
122738
122738
I have two tables.[Table.Game] Columns are "PK_id" "username" and "couponID" [Table.Coupons] Columns are "PK_id" "CouponID" and "Points" The two columns "CouponID" are associated with eachother. Let say i have two rows with the user "harry" in [Table.Game] this person has two different couponID. In [Table.Coupons] this user "harry" has "CouponID" 1 and 2. Column "Points" have 10 and 20. To the question how do u sum this two different point values that have different "CouponIDs". This does work if i have only one "CouponId". But not when the user has 2 different CouponIDs. Values is 0
var points = (from p in linq.Coupons
join g in linq.games on p.couponID equals g.couponID
where g.username == username && g.couponID == p.couponID
select (int)p.win).Sum();
Is it possible to have ON and WHERE , both keyword using Joins in a single query
View 6 RepliesI have a select statement. I need to get a field value from the first statement. GOAL: Below is what I am trying to accomplish - the code below is not correct - Please modify the statement with the correct SQL Syntax. I want to query a table and based on the results I want to set the "If THE Else"statement to query then table with diffent inner joins.
SELECT * FROM CONTENTITEM CI
INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID
IF CI.FORMTYPE = 200 OR 300
SELECT * FROM CONTENTITEM CI
INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID
INNER JOIN CONTENTFORMFIELD CF ON CF.ITEMID = CI.ITEMID
WHERE CI.ITEMID = @ITEMID
ELSE
SELECT * FROM CONTENTITEM CI
INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID
WHERE CI.ITEMID = @ITEMID
I have a SQL select statement with inner joins to get a total number of records for the statement. I have tried using count with group by's but it just counts one for each record instead of giving me the total number of records for the query. Here is the query:
[Code]....
I have one question regarding how to make the below mentioned type of query more optimized so that mine query perfomace can be increased.
Select a.name1,a.address1,b.field3,c.field4,d.field6,e.field7
from a
inner join b on a.id=b.id
inner join c on c.id=b.id
inner join d on d.id=c.id
iner join e on e.id=d.id
union all
Select a1.name1,a1.address1,b1.field3,c.field4,d.field6,e.field7
from a1
inner join b1 on a1.id=b1.id
inner join c on c.id=b1.id
inner join d on d.id=c.id
iner join e on e.id=d.id
union all
Select a2.name1,a2.address1,b2.field3,c.field4,d.field6,e.field7
from a2
inner join b2 on a2.id=b2.id
inner join c on c.id=b2.id
inner join d on d.id=c.id
iner join e on e.id=d.id
All the above three select query at last using the same inner join condition, means last two inner join condition are the same for all the select query.
Is there any way so that this query can be optimized?
Inner Join:- Returns a row when there is matching table in both tables. we can use comparison operator like =,<,>,<>
I created two tables
TableA
ID OID Type
1 1 A
2 6 B
3 7 C
4 10 D
TableB
ID Value
1 10
2 30
3 40
5 60
6 70
Basic Inner Join select Type From TableB as a inner join TableA as b on a.ID=b.OID Result Type
A
B
If i want only the type which has not match in tableB then i can use not in subquery i get the result if i m using select Type From TableB as a inner join TableA as b on a.ID<>b.OID then i get
Type
A
A
A
A
B
B
B
B
C
C
C
C
C
D
D
D
D
D
18 rows affetected i m suprise
I have a web service with a dataset and I need to fill a gridview in my web page with data from multiple tables (like 4). How can I retrieve data from my 4 tables and construct a datatable with a table adapter in the dataset? I need to create this in the web service to bind the gridview in my page. In SQL I create a view with the joins of the 4 tables and added the view to the dataset. It works fine, but for other windows in my web page I need to use like 8 trough 10 tables (just to retrieve one field of each one) and I don't know if making that huge joins is the more efficient way to retrieve data from the server. There is another way to make joins? (Make separate queries for each table and create one table in memory) How?
View 2 Replieshow to give a multiple inner joins to the single table
View 1 RepliesOne of my peer wrote a query based on multiple tables using joins. One of the table is in a different database The query was taking much time
1. Create an SP
2. Create a temp table and copy all records from the external db
3. Avoid using Left Join as much as possible and use Inner Join
4. Create and use indexes
5. Remove tables/columns that are not necessary
In this scenario, I would like to suggest to use Covering Index, but how can it be created for multiple tables and a temp table?
Executed a linq query, and now needs to bind it to the gridview. here is code that doesn't bind anything currently:
[Code]....
I have a web page with a GridView and a table outside the gridview to display more information of the record when it is selected (like record details). I'm using data of five tables for this particularly window. I use one table of the five to bind the gridview the other tables are for the details, but I use like 1 or 2 fields of those tables. I'm binding the page with web services. I need some advice on which is the better way for doing this to make a faster web page.
In sql server I made a view with the five tables (all the tables relations are working perfectly). In the web service I call the view; and in the web page I call the web service to bind the page. Everything is working. All I want to do is retrieve from the server all the necessary data only once when the page loads and bind the five tables data (like 18 fields total) in the gridview which I only show 12 fields the other 6 fields are hidden, those 6 are for the details which I get with jQuery (that's another topic). Bind everything only once and manage the data on client side. Everything is working perfectly. Here is my doubt.
How are the maximum table joins I can make? To retrieve the data via web and not get a server time out error. Which is a better way to make a faster page? Make 2 views one for the gridview and one for the details and put each view in a web service; or make one view with all the table joins and one web service. Right now all is working but I have to make other pages that use different tables and joins and I don't know if the server gets me the time out error when the page is fully working.
The gist of the problem is that we have an alumni table (one record per person) and also a couple of other tables (one to many) that have degree info and interest info. In a search screen in our app you can search for criteria that spans all three tables (there are actually more fields and tables than shown in the example below but I am trying to keep it simple).
The code below works (properly returns people without degrees for example) but still feels a little clunky or over-engineered to me. Are there easier ways to do this? NOTE: I have been through quite a few iterations/approaches to making the correct data be returned.
[code]....
I have some problems trying to query my database. The problem is that I'm not sure how to multiple join tables in my database.
tables to make it easier:
[code]....
What I'm supposed to do is to find all the teachers (predavac) who were teachers on the specific activity (aktivnost) and who had specific attendee (polaznik). The problem is I'm not sure how to do multiple joins on this one.
In our organization, we are still on .net 1.1 environment, using javascript, a few open source applications/widgets. Development is done using Visual Studio 2003, grid view, and iframes. Our application works in Internet Explorer 7 and IE 8 (in compatibilily mode).give any basic steps we can take to get our application to work cross browsers? What are the starting locations we can get at to start making existing code work in different browsers?
View 5 RepliesI am currently a beginner in asp.net i am currently watching msdn videos of "beginners developers learning" i am on the topic of application state so i got confused on a topic of "cross page postback" can anybody explain what that is
and another thing is "profile" object we use it in web.config file
1. what is cross page post back?
2. what is web.config file?
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.
EDIT: At first I thought it wasn't working cross domain at all, now I realize it only works in IE. I'm using jQuery to call a web service (ASP.NET .axmx), and trying to us jsonp so that I can call it across different sites. Right now it is working ONLY in IE, but not in Firefox, Chrome, Safari. Also, in IE, a dialog pops up warning "This page is accessing information that is not under its control..."
$.ajax({
type: "POST",
[URL],
dataType: "jsonp",
success: function(data) {
alert(data.prop1);
},
error: function(XMLHttpRequest, textStatus, errorThrown) {
alert(XMLHttpRequest.status + " " + textStatus + " " + errorThrown);
}
});
And the server code is:
[ScriptService]
public class TestService : System.Web.Services.WebService{
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public void HelloWorld() {
string jsoncallback = HttpContext.Current.Request["jsonp"];
var response = string.Format("{0}({1});", jsoncallback, @"{'prop1' : '" + DateTime.Now.ToString() + "'}");
HttpContext.Current.Response.Write(response);
}
}
I need to validate a coming request where form is being submitted from the another domain to my website,
Like
[URL]
Remote site:
[URL]
Now this submit.aspx form get submitted to my website,
how to validate on [URL] that request is coming from [URL] only.
I am building a website, within a large intranet, that wraps and adds functionality to another site within the same intranet. I do not have access to the other site's source and they do not provide any api's for the functionality they provide. I need to, somehow, have my server-side code go to that site, fill in some forms, then press a submit button. Is this possible? If so, how can I accomplish this?
Note: I am working in asp.NET if that matters at all.
we host catalog data and UI for our clients. we do not build entire web site for them. so for the time being client puts an iframe and points URL to our hosting site. it was working fine but now just because of iframe they are lossing SEO.so my question is how to achieve my goal without iframe.
1) how can i embed/inject/merge UI hosted by US to our client web site.
2) Get SEO benefits