Solved

c#, LINQ Group By

Posted on 2014-11-14
11
333 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
  • 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 33

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 33

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 33

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 33

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 33

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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