ADO.NET :: Linq To Sql - Possible Optional Joins?
Nov 22, 2010
Is 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.
View 2 Replies
Similar Messages:
Mar 10, 2011
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();
View 2 Replies
Jun 21, 2010
I 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.
View 5 Replies
Mar 14, 2010
Executed a linq query, and now needs to bind it to the gridview. here is code that doesn't bind anything currently:
[Code]....
View 3 Replies
Jul 2, 2010
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]....
View 1 Replies
Jan 28, 2011
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.
View 1 Replies
Mar 10, 2011
does anybody knows if EF 4.0 supports cross database joins for its entities?
View 1 Replies
Jun 24, 2010
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
View 5 Replies
Jul 29, 2010
Is it possible to have ON and WHERE , both keyword using Joins in a single query
View 6 Replies
Sep 14, 2010
I 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
View 10 Replies
Mar 17, 2010
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]....
View 3 Replies
Mar 22, 2010
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?
View 2 Replies
Jan 29, 2011
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
View 3 Replies
Mar 25, 2011
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 Replies
Oct 30, 2012
how to give a multiple inner joins to the single table
View 1 Replies
Nov 15, 2010
One 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?
View 3 Replies
Mar 21, 2011
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.
View 1 Replies
Oct 5, 2010
I have the following scenario: my website displays articles (inputted by an admin. like a blog).So to view an article, the user is referred to Home/Articles/{article ID}.However, the user selects which article to view from within the Articles.aspx view itself, using a jsTree list.So I what I need to do is to be able to differentiate between two cases: the user is accessing a specific article, or he is simply trying to access the "main" articles page. I tried setting the "Articles" controller parameter as optional (int? id), but then I am having problems "using" the id value inside the controller.
View 7 Replies
Feb 1, 2010
I have an MVC action on my abstract BaseControlller like this (and this action is common across all inheriting controllers):
//
// GET: /controller/RenderForm/{formType}
[Authorize(Roles = "Administrators")]
public ActionResult RenderForm(FormType formType, BaseContentObject contentObject)
[code]...
View 1 Replies
Jan 19, 2011
I want to create a method with optional parameters in my wcf service. I am doing it like this
[Code]....
When I try to use it in my client application and try to pass the values only for first two parameters it gives me an error that"Argument not sepecified for parameter Status"
View 4 Replies
Feb 24, 2011
Wondering if you could give me a nudge in the right direction. I'm working on an app where I need to validate a field for a correct URL. However, this is an optional field and may be left blankI was just wondering, how can I bypass the validator if the field is null.
View 5 Replies
Jul 24, 2010
I want to declare Optional Parameter in C#.As in VB.Net we have OPTIONAL Keyword,In C# is there any way or we have to pass all the arguments in C#?
VB.NET CODE
[code]....
View 8 Replies
Oct 29, 2010
I have got a problem about setting the optional parameter to NULL in sql sp. Initially I set all the parameters null in the sql sp. That's OK. But I would like to set the parameters to Null again if a particular parameter has a value of - 1 by using the "IF ELSE" statement block. However, the problem was that I was unable to set the parameter to NULL in the IF ELSE statement. So please correct my IF ELSE statement in the store procedure. This is really urgent and important for my project.
spGetMealsByNutritions]
@MealTypeID Int,
@Gluten Bit = Null,
@Dairy Bit = Null,
@Egg Bit = Null,
@Fish Bit = Null,
@Veg Bit = Null,
@Pork Bit = Null,
@Beef Bit = Null,
@Soy Bit = Null,
@Sesame Bit = Null
[code]...
View 7 Replies
Mar 23, 2010
What is the best way to handle following situation? A dropdown(for master table) is optional in a particular form. But, In database table the field is constrained with foreign key. If user don't select from dropdown then It creates problem because of foreign key. One solution is to create default option in master table and use it in case of blank selection. but in dropdown, we need to handle this to show on top. Is it perfect solution?
View 1 Replies
Jan 17, 2011
I have a timespan ts that has mostly minutes and seconds and sometimes hours. I'd like to format a string that'll give these results depending on the data
3:30 (hours not displayed, showing only full minutes)
13:30
1:13:30 (shows only full hours instead of 01:13:30)
So far I have
string TimeSpanText = string.Format("{0:h\:mm\:ss}", MyTimeSpan);
but it's not working.
View 3 Replies
