Solved

c#, LINQ Group By

Posted on 2014-11-14
11
344 Views
Last Modified: 2016-02-15
wscgsContext.Search returns recordset with fields like [First Name], [Last Name], and [City].

Question 1: wscgsContext.Search <-- shows an error:
WSCGSoftwareDataContext does not contain a definition for 'Search' and no extension method accepting a first argument of type WSCGSoftwareDataContext could be found (are you missing a using directive or an assembly reference?).

Question 2: How can I complete the LINQ on line 5 to group and sort it for [City] only?

Optionally we can use Distinct if it has a better performance.
        string cs = ConfigurationManager.ConnectionStrings["WSCGSoftwareConnectionString"].ConnectionString;
        SqlConnection cnn =new SqlConnection(cs);
        WSCGSoftwareDataContext wscgsContext =  new WSCGSoftwareDataContext(cnn);
FYI: I have  "public partial class Search" in WSCGSoftware.Designer.cs.
If assembly is missing, I cannot figure out what is missing. Could this possibly be a name space issue?

        dynamic outputs = from output in wscgsContext.Search select output.City;

        foreach (WSCGSoftwareDataContext search in outputs)
        {
            cmbCity.Items.Add(outputs.City);
        }

Open in new window

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444424
How do you implement, "public partial class Search" in WSCGSoftware?
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444532
I have  "public partial class Search" in WSCGSoftware.Designer.cs. This is automatically created.
If assembly is missing, I cannot figure out what is missing. Could this possibly be a name space issue?

Search table is a temp table. At this point it doesn't have PK in it if this makes any difference.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444534
Can you post it here please?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444544
Here you are:dbml_filePlease note #pragma warning disable 1591at line 1 in WSCGSoftware.Designer.cs :
#pragma warning disable 1591
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.18444
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;



[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="WSCGSoftware")]
public partial class WSCGSoftwareDataContext : System.Data.Linq.DataContext
{
	
	private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
	
  #region Extensibility Method Definitions
  partial void OnCreated();
  #endregion
	
	public WSCGSoftwareDataContext() : 
			base(global::System.Configuration.ConfigurationManager.ConnectionStrings["WSCGSoftwareConnectionString"].ConnectionString, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(string connection) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(System.Data.IDbConnection connection) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public WSCGSoftwareDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
			base(connection, mappingSource)
	{
		OnCreated();
	}
	
	public System.Data.Linq.Table<Search> Searches
	{
		get
		{
			return this.GetTable<Search>();
		}
	}
}

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Search")]
public partial class Search
{
	
	private string _First_Name;
	
	private string _Last_Name;
	
	private string _Address;
	
	private string _City;
	
	private string _State;
	
	private string _Zip_Code;
	
	private string _Phone;
	
	private string _Email;
	
	private string _Order_Date;
	
	private string _Title;
	
	private string _Unit_Price;
	
	private string _Quantity;
	
	public Search()
	{
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[First Name]", Storage="_First_Name", DbType="VarChar(30)")]
	public string First_Name
	{
		get
		{
			return this._First_Name;
		}
		set
		{
			if ((this._First_Name != value))
			{
				this._First_Name = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Last Name]", Storage="_Last_Name", DbType="VarChar(30)")]
	public string Last_Name
	{
		get
		{
			return this._Last_Name;
		}
		set
		{
			if ((this._Last_Name != value))
			{
				this._Last_Name = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Address", DbType="VarChar(100)")]
	public string Address
	{
		get
		{
			return this._Address;
		}
		set
		{
			if ((this._Address != value))
			{
				this._Address = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_City", DbType="VarChar(30)")]
	public string City
	{
		get
		{
			return this._City;
		}
		set
		{
			if ((this._City != value))
			{
				this._City = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_State", DbType="VarChar(30)")]
	public string State
	{
		get
		{
			return this._State;
		}
		set
		{
			if ((this._State != value))
			{
				this._State = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Zip Code]", Storage="_Zip_Code", DbType="VarChar(20)")]
	public string Zip_Code
	{
		get
		{
			return this._Zip_Code;
		}
		set
		{
			if ((this._Zip_Code != value))
			{
				this._Zip_Code = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Phone", DbType="VarChar(20)")]
	public string Phone
	{
		get
		{
			return this._Phone;
		}
		set
		{
			if ((this._Phone != value))
			{
				this._Phone = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Email", DbType="VarChar(100)")]
	public string Email
	{
		get
		{
			return this._Email;
		}
		set
		{
			if ((this._Email != value))
			{
				this._Email = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Order Date]", Storage="_Order_Date", DbType="VarChar(30)")]
	public string Order_Date
	{
		get
		{
			return this._Order_Date;
		}
		set
		{
			if ((this._Order_Date != value))
			{
				this._Order_Date = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Title", DbType="VarChar(30)")]
	public string Title
	{
		get
		{
			return this._Title;
		}
		set
		{
			if ((this._Title != value))
			{
				this._Title = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Unit Price]", Storage="_Unit_Price", DbType="VarChar(30)")]
	public string Unit_Price
	{
		get
		{
			return this._Unit_Price;
		}
		set
		{
			if ((this._Unit_Price != value))
			{
				this._Unit_Price = value;
			}
		}
	}
	
	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Quantity", DbType="VarChar(10)")]
	public string Quantity
	{
		get
		{
			return this._Quantity;
		}
		set
		{
			if ((this._Quantity != value))
			{
				this._Quantity = value;
			}
		}
	}
}
#pragma warning restore 1591

Open in new window

WSCGSoftware.dbml.layout :
<?xml version="1.0" encoding="utf-8"?>
<ordesignerObjectsDiagram dslVersion="1.0.0.0" absoluteBounds="0, 0, 11, 8.5" name="WSCGSoftware">
  <DataContextMoniker Name="/WSCGSoftwareDataContext" />
  <nestedChildShapes>
    <classShape Id="a37c36b4-c163-48d7-9792-a3cbfa22cb55" absoluteBounds="0.5, 0.5, 2, 3.1170068359375">
      <DataClassMoniker Name="/WSCGSoftwareDataContext/Search" />
      <nestedChildShapes>
        <elementListCompartment Id="116a49d8-f500-4d51-98bc-ee1d88ce3bcf" absoluteBounds="0.51500000000000012, 0.96, 1.9700000000000002, 2.5570068359375" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
      </nestedChildShapes>
    </classShape>
  </nestedChildShapes>
</ordesignerObjectsDiagram>

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444547
Hi eghtebas;

Change this line in your code:

dynamic outputs = from output in wscgsContext.Search select output.City;

to this:

dynamic outputs = from output in wscgsContext.Searches select output.City;

The mapping between the code and the database table is Searches (In code)  To  Search (Database Table)
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444563
Thank you very much for the correction, now I have a similar confusion in the following lines:

       foreach (WSCGSoftwareDataContext output in outputs)
        {
            cmbCity.Items.Add(outputs.City);
        }

Cannot convert type 'string' to 'WSCGSoftwareDataContext'
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444567
In answer to question two try it like this.
dynamic outputs = (from output in wscgsContext.Searches
                   group output by new { Key = output.City } into cityGroup
                   select cityGroup.Key).Distinct().OrderBy(c => c );

Open in new window

0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40444571
Hi eghtebas;

That is because the query is returning a collection of type String, the City column values. Try it like this.

       foreach (String output in outputs)
        {
            cmbCity.Items.Add(outputs);
        }

Open in new window

0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40444575
cmbCity.Items.Add(outputs); without s works for the old code.

But, with the new (group by code), at foreach I get: Cannot order by type '<>f__AnonymousType0`1[System.String]'.

Just to test without order by, when I used:
       dynamic outputs = (from output in wscgsContext.Searches
                         group output by new { Key = output.City } into cityGroup
                           select cityGroup.Key).Distinct();//.OrderBy(c => c);

        foreach (String output in outputs)
        {
            cmbCity.Items.Add(output);
        }

Open in new window

I got Cannot convert type '<>f__AnonymousType0<string>' to 'string' at foreach (String output in outputs)
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40444620
Thank you Fernando for wonderful help. I am closing this question and open a new question for pending group by/distinct part. I will post a link here.

Regards,

Mike

Please see: http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_28562829.html
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444706
Not a problem Mike, glad to help.

Will look at new question.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What are MicroServices? 4 67
.NET Redemption/Outlook Examples 3 32
search by any word in column 5 29
Need a quick overview of Selenium testing 2 35
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question