ADO.NET :: Get Distinct Value From A Group-Join Syntax Linq?

Sep 9, 2010

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

View 3 Replies


Similar Messages:

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 :: 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

SQL Server :: Incorrect Syntax Near The Keyword 'distinct'

Dec 8, 2010

I have the data below:

id locationid cookieid username IPAddress createdate
1 704 192.168.0.103 2010-12-08 18:11:24.423
2 704 192.168.0.103 2010-12-08 18:13:26.133
3 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:13:47.710
4 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:16:42.730
5 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:17:00.433
6 704 myname@hotmail.com 192.168.0.103 2010-12-08 18:18:09.867
7 1504 myname@hotmail.com 192.168.0.103 2010-12-08 18:26:01.447

Now, I want to select all unique locationid's from a single username, its important that the results can be PAGED and SORTED, I now have:

SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY createdate) as RowNum,
distinct(locationid), l.*
FROM location_views lv
INNER JOIN locations l on l.id=lv.locationid
where lv.username='myname@hotmail.com'
) as info
WHERE RowNum > 0 AND RowNum <= 100

But this throws the error: "Incorrect syntax near the keyword 'distinct'."

View 2 Replies

C# - Sql Syntax How To Do A Join

Mar 24, 2011

Im trying to retrieve a path name from my Pictures table the pathname is stored under picturepath in my Pictures table I dont know how to join it on to my current sql syntax it uses the UserID which is set by a session.

And im unsure what goes in the commented line for my image url string?

using (OdbcCommand cmd = new OdbcCommand("SELECT Wallpostings FROM WallPosting WHERE UserID=" + userId + " ORDER BY idWallPosting DESC", cn))
{
using (OdbcDataReader reader = cmd.ExecuteReader())
{
test1.Controls.Clear();
while (reader.Read())
{
System.Web.UI.HtmlControls.HtmlGenericControl div = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
div.Attributes["class"] = "test";
//div.Style["float"] = "left";
div.ID = "test";
Image img = new Image();
img.ImageUrl = String.Format("{unsure}", reader.GetString(unsure));
// this line needs to be represented in sql syntax
img.AlternateText = "Test image";
div.Controls.Add(img);
div.Controls.Add(ParseControl(String.Format("{0}", reader.GetString(0))));
div.Style["clear"] = "both";
test1.Controls.Add(div);

View 1 Replies

Access The Group Of A Linq Group - By Query From A Nested Repeater Control?

Mar 26, 2010

I'm using a linq group by query (with two grouping parameters) and would like to use the resulting data in a nested repeater.

var dateGroups = from row in data.AsEnumerable()
group row by new { StartDate = row["StartDate"], EndDate = row["EndDate"] };
"data" is a DataTable from an SqlDataAdapter-filled DataSet. "dateGroups" is used in the parent repeater, and I can access the group keys using Eval("key.StartDate") and Eval("key.EndDate").

Since dateGroups actually contains all the data rows grouped neatly by Start/End date, I'd like to access those rows to display the data in a child repeater. To what would I set the child repeater's DataSource? I have tried every expression in markup I could think of; I think the problem is that I'm trying to access an anonymous member (and I don't know how.) In case it doesn't turn out to be obvious, what would be the expression to access the elements in each iteration of the child repeater? Is there an expression that would let me set the DataSource in the markup, or will it have to be in the codebehind on some event in the parent repeater?

View 3 Replies

DataSource Controls :: Equivalent Of Group Join In Lambda Expression?

Jun 29, 2010

What will be the equivalent GroupJoin lambda expression in LINQ when I do this.

Dim fruit_salad = (From fruits In _DataContext_Fruits _
Group Join fruitRipe In _DataContext_FruitRipe On fruitRipe.fID Equals fruits.fID Into Group
From fruitty_lecious In Group.DefaultIfEmpty() _

[code]...

View 5 Replies

ADO.NET :: How To Get The Distinct Value Form DataTable Using LINQ

Oct 27, 2010

I would like to get the distinct value form DataTabel using LINQ.

For example my sample table,

[code]....

1. How to get the distinct Country from Country you like column and the return records as Array or DataTable? (I want only India,USA,UK,Germany )

Is there any way to filter the record values using LINQ?

View 3 Replies

DataSource Controls :: Linq Distinct Ip According To Day?

May 4, 2010

I'm trying the pull out a result that shows me how many visitors I have had each day.

My database looks like this.

[code]....

View 5 Replies

ADO.NET :: Linq To Sql Using Distinct On Individual Values?

Jul 29, 2010

I have a database that is filled with documents. In the database there is the documents id, name and version. Two documents can have the same name and id but are seperated by different versions.For instance there can be two entries, id: A44, name: Dilbert was here. But they are version A and B.When retrieving these to a listview I wish to limit the documents shown to only one version. Doing so means that I am only to show one document with the same id. I only get the id and name to show in the listview so I thought that distinct might do the trick. But since the name can change throughout different versions this was no good.Basically I wish to retrieve all documents but with distinct id. How do I achieve this?

View 10 Replies

Linq To DataTable Not Producing Distinct Values?

Oct 19, 2010

I have a datatable which has been dynamically generated from FoxPro tables using a UNION Select statement. e.g.

SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM Z ORDER By v_alue1

This produces a datatable with about 100 rows, each containing many fields, one of which is c_olor. From this datatable, I would like to select the distinct colors and then output in a dropdown.

I have a public class Color which just has one property which I can then use as the DataTextField and DataValueField for the dropdownlist

[code]...

However this never results in the distinct colors.

I have searched and searched for what I am looking for, and this seems to be one of the methods to produce a distinct set of results, but this and the others do not work.

My reasoning behind getting the colors this way, is that I need to get various other distinct values from the same UNION SELECT datasource, so would just do one DB call, cache the results, and then just used this cached datasource to retrieve all my distinct values.

View 2 Replies

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 :: Join 3 Table In LINQ?

Oct 10, 2010

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

View 1 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 :: Put A Right Outer Join On Linq Statements?

Aug 2, 2010

I am trying to put a Right Outer Join on Linq statements. how to put Right Outer Join on Linq.

View 1 Replies

C# - Join Is Not Working In LINQ Statement?

Mar 23, 2011

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

View 3 Replies

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

ADO.NET :: Use An Expression On A Linq Outer Join?

Nov 18, 2010

private void GetResults()

Use an Expression on a Linq Outer Join

View 1 Replies

ADO.NET :: LINQ Query With Multiple Join?

Aug 18, 2010

I ned to run a query with two joins, the second join does not work please help with the correct syntax.

[Code]....

View 2 Replies

ADO.NET :: Left Join And Grouping In LINQ?

Aug 11, 2010

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

View 10 Replies

C# - Print After Join Linq Query?

Mar 15, 2011

I have this code:

public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();

[code]...

View 3 Replies

DataSource Controls :: Like Command In SQL Error When Put Join In Text Box Provided For Searching Incorrect Syntax Near "kae"

Apr 17, 2010

i have a radiobutton list and i want the select statement of a SqlDataSource to change according to which radiobutton is selected. My code is as follows

[Code]....

It is giving me the following error when i put jon in the Text Box provided for searching Incorrect syntax near 'kae'. Incorrect syntax near 'Jon'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'jon''.Source Error:

[Code]....

Line 29: query = query + "WHERE ([Ufname] LIKE '%'" + txtSrch.Text + "'%')";
Line 30: SqlDataSource1.SelectCommand = query;
Line 31: SqlDataSource1.Select(DataSourceSelectArguments.Empty);
Line 32: }
Line 33:

View 11 Replies

Mvc Nerddinner Question Linq To Entities Join?

May 13, 2010

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

View 1 Replies

Timeout During Left Join Using LINQ To Entities?

Mar 2, 2011

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().

View 1 Replies







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