C# - Linq Sum Columns Including Joins?
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
Similar Messages:
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
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
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 3, 2010
I have a gridview control which gets data from database. One of the column is a checkbox and based on the value of the checkbox, I need to display 2 more columns. Currently I am having 2 issues:
1. I am able to achieve this when OnDataBound event gets triggered. Basically I am checking the row state and type and if it is in Normal mode I check the status of the checkbox and based on the status I am setting the style on the columns I want to hide (columns contain checkboxes). But for some reason, I am not able to hide the header. The GridView shows the header but the text boxes which are in the columns are hidden.
2. During Edit mode, I have attached a javascript to the checkbox and when the status of the checkbox changes, it hide/unhides the columns, again header cannot be made hidden. Also, if I use the Visible property of the header, I can make the header invisibly (only using the code behind) but when javascript is invoked on the checkbox during edit mode, it cannot find the controls as they are not visible and hence does not do anything. Since TemplateField does not support style property I can't even set the style["display"].
View 10 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
Aug 17, 2010
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?
View 2 Replies
Mar 23, 2011
I have two tables:
Entity
ID (PK), int
Name
Descrip
Users
ID (PK)
EntityID, int (this is not connected to Entity table)
Now I am using LINQ to pull the records which has a Entity.ID = something. Which will show me couple of records in my GridView. Here is my LINQ statement:
protected void Page_Load(object sender, EventArgs e)
{
string getEntity = Request.QueryString["EntityID"];
int getIntEntity = Int32.Parse(getEntity);
OISEntityTestingDataContext db = new OISEntityTestingDataContext();
//OISLinqtoSQLDataContext db = new OISLinqtoSQLDataContext();
var tr =
from r in db.Users
join s in db.Entities on r.UserID equals s.ID
where s.ID == getIntEntity
select new
{
//To Show Items in GridView!
};
GridView1.DataSource = tr;
GridView1.DataBind();
}
Now here I am getting an error mesg on 'join' The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join What does that mean?
View 3 Replies
Feb 26, 2011
I have a query which returns multiple columns from the database using LINQ
var donors = from d2 in db.Donors
where d2.Status == "Pending"
select new { donorID = d2.donorID, bloodGroup = d2.bloodGroup, contactNo = d2.contactMobile, status = d2.Status };
now I want to display the results in different Labels accessing one column value from donors resultset.
ex:
Label1.Text = donorID;
Label2.Text = bloodGroup; ...and so on
View 2 Replies
May 11, 2010
If I have a linq statement like:
[Code]....
There may be a lot of data in the Blogs table. What if all I wanted was say BlogID, BlogTitle and BlogAuthor and nothing else, is there a way to do this in an effort to make the sql more efficent?
Along the same lines in my DataContext (.dbml) file I added some associations between some of my tables. This makes coding nice when I do a linq statement that will join those tables automatically for me but my concern is does it do that everytime, thus making all my linq quieries inefficient?
View 1 Replies
Jul 17, 2010
I am trying to Fill my dropdownlist with two column as textfield and 1 column as value field.
dropdownlist2.items.add(new listitem(??? , ??? )
I used Foreach and many many options , I can assing the textfield easy but value I can't
Dim db1
As
New TripsLinqDataContextDim
listhotel = From th
In db1.Triphotels _
Where th.TripCountryID = cid _
Select
CStr(th.TripHotelID) +
" - " + th.City.TripCityName +
" - " + th.TripHotelName
ListBox1.DataSource = listhotel
ListBox1.DataBind()
This will list hotelname and id and city , but I need to set the Triphotelid as value of item in dropdownlist
View 1 Replies
May 8, 2010
how to write that below query in linq
DECLARE @Name varchar(20)
Select @Name = empFirstname+','+empLastName+','+empBankCode From srpEmployee
PRINT @Name
View 7 Replies
Apr 19, 2010
I have just started using LINQ with VS2008. I have added extra columns to my table, saved the changes, but class ClassName.Designer.CS didn't get updated. What do I have to do to updated the class? Do I have to do it manually, or is there a way to automatically synchronize tables and ClassName.Designer.CS?
View 2 Replies
Aug 3, 2010
I am using dynamic data entities linq to sql project. For my test project I have one table, with 10 columns and a single primary key for example. Almost all the columns are varchars.I can get the basic project up and running fine - update, delete, insert etc.ince it has no foreign keys, there are no search filters rendered. I was wondering if there was an easy way to generate a set of filters for the columns for this table. So a First name and last name search box. Is there a way to generate these filters for chosen columns and is there a way to generate these for all the columns
for example, above each of the columns in the grid there might be a text box ( I am not too fussed where the text boxes are rendered as such). Each of the text boxes will act as a filter for that column (doing a SQL 'like')
View 1 Replies
Jan 26, 2010
I have the following code:
[Code]....
With this code behind:
[Code]....
Initially, I wrote the GetTeamMember() method to display the FullName in the ItemTemplate.
Then as I wrote the EditTemplate, I needed to populate a DropDownList with a list of names (first and last). It was fairly easy using the LinqDataSource, however it currently only shows the LastName. I could write another method GetTeamMembers() to populate the ddlTeamMember with the data I want, but I thought maybe there's a better "LINQ" way.
So I'm looking for a better way to get FullName (i.e. Firstname + " " + LastName) into both lblTeamMember and ddlTeamMember.
Options I thought of are:
I could write a method GetTeamMembers() I write a StoredProc that returns the extra column Better way?
View 6 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
Aug 3, 2010
i am making relation between two datatables and m getting this error
in this ds.Tables(0).Columns("In_ID") datatype is string
ds.Tables(1).Columns("row_id") datatype is integer
how i do the type casting here to make dataset relation
ds.Relations.Add("Rel_1", ds.Tables(0).Columns("In_ID"), ds.Tables(1).Columns("row_id"), True)
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