views:

6123

answers:

12

UPDATE

I'm basically binding the query to a WinForms DataGridView. I want the column headers to be appropriate and have spaces when needed. For example, I would want a column header to be First Name instead of FirstName.


How do you create your own custom column names in LINQ?

For example:

Dim query = From u In db.Users _
            Select u.FirstName AS 'First Name'
A: 

I dont see why you would have to do that, if you are trying to do that for a grid or something, why not just name the header in the HTML?

James Hall
+1  A: 

What you would actually be doing is setting a variable reference to the return, there is not a way to name a variable with a space. Is there an end result reason you are doing this, perhaps if we knew the ultimate goal we could help you come up with a solution that fits.

Quintin Robinson
+6  A: 

As CQ states, you can't have a space for the field name, you can return new columns however.

var query = from u in db.Users
            select new
            {
                FirstName = u.FirstName,
                LastName = u.LastName,
                FullName = u.FirstName + " " + u.LastName
            };

Then you can bind to the variable query from above or loop through it whatever....

foreach (var u in query)
{
   // Full name will be available now 
   Debug.Print(u.FullName); 
}

If you wanted to rename the columns, you could, but spaces wouldn't be allowed.

var query = from u in db.Users
            select new
            {
                First = u.FirstName,
                Last = u.LastName
            };

Would rename the FirstName to First and LastName to Last.

Scott Nichols
A: 

You can use the 'let' keyword:

Dim query = From u In db.Users _
            let First_Name = u.FirstName
            Select First_Name

As other answers here have shown, this isn't useful. However, the let keyword is useful for doing more complex queries (this example is off the top of my head and doesn't require the let keyword to work):

from x in dc.Users 
let match = regex.Match(".*ass.*", x.Name) 
let comment = match ? "*snicker*" : "sup"
select new { Name = x.Name, Comment = comment };
Will
A: 

My VS2008 is busted right now, so I can't check. In C#, you would use "=" - How about

Dim query = From u In db.Users _
            Select 'First Name' = u.FirstName
Steve Owens
A: 

Sorry for not elaborating on what I'm trying to achieve by this. I'm basically binding the query to a GridView. I want the column headers to be appropriate and have spaces when needed. For example, I would want a column header to be First Name instead of FirstName.

Bryan Roth
I'd go with the answer above that specifies the text in the GridView definition.
KevDog
+2  A: 

You can make your results have underscores in the column name and use a HeaderTemplate in a TemplateField to replace underscores with spaces. Or subclass the DataControlField for the GridView and override the HeaderText property:

namespace MyControls 
{
public SpacedHeaderTextField : System.Web.UI.WebControls.BoundField
 { public override string HeaderText
    { get 
       { string value = base.HeaderText;
         return (value.Length > 0) ? value : DataField.Replace(" ","");
       }
      set
       { base.HeaderText = value;
       }     
    }
 } 
 }

ASPX:

<%@Register TagPrefix="my" Namespace="MyControls" %>

<asp:GridView DataSourceID="LinqDataSource1" runat='server'>
  <Columns>
     <my:SpacedHeaderTextField DataField="First_Name" />
  </Columns>
</asp:GridView>
Mark Cidade
+8  A: 

If you want to change the header text, you can set that in the GridView definition...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />
    </Columns>
</asp:GridView>

In the code behind you can bind to the users and it will set the header to First Name.

protected void Page_Load(object sender, EventArgs e)
{
     // initialize db datacontext
     var query = from u in db.Users
                 select u;
     GridView1.DataSource = query;
     GridView1.DataBind();
}
Scott Nichols
A: 

As others have already pointed out, if the header title etc is known at design time, turn off AutoGeneratedColumns and just set the title etc in the field definition instead of using auto generated columns. From your example it appears that the query is static and that the titles are known at design time so that is probably your best choice.

However [, although your question does not specify this requirement] - if the header text (and formatting etc) is not known at design time but will be determined at runtime and if you need to auto generate columns (using AutoGenerateColumns= true") there are workarounds for that.

One way to do that is to create a new control class that inherits the gridview. You can then set header, formatting etc for the auto generated fields by overriding the gridview's "CreateAutoGeneratedColumn". Example:

//gridview with more formatting options
namespace GridViewCF
{
    [ToolboxData("<{0}:GridViewCF runat=server></{0}:GridViewCF>")]
    public class GridViewCF : GridView
    {
        //public Dictionary<string, UserReportField> _fieldProperties = null;

        public GridViewCF()
        {
        }

        public List<FieldProperties> FieldProperties
        {
            get
            {
                return (List<FieldProperties>)ViewState["FieldProperties"];
            }
            set
            {
                ViewState["FieldProperties"] = value;
            }
        }

        protected override AutoGeneratedField CreateAutoGeneratedColumn(AutoGeneratedFieldProperties fieldProperties)
        {
            AutoGeneratedField field = base.CreateAutoGeneratedColumn(fieldProperties);
            StateBag sb = (StateBag)field.GetType()
                .InvokeMember("ViewState",
                BindingFlags.GetProperty |
                BindingFlags.NonPublic |
                BindingFlags.Instance,
                null, field, new object[] {});

            if (FieldProperties != null)
            {
                FieldProperties fps = FieldProperties.Where(fp => fp.Name == fieldProperties.Name).Single();
                if (fps.FormatString != null && fps.FormatString != "")
                {
                    //formatting
                    sb["DataFormatString"] = "{0:" + fps.FormatString + "}";
                    field.HtmlEncode = false;
                }

                //header caption
                field.HeaderText = fps.HeaderText;

                //alignment
                field.ItemStyle.HorizontalAlign = fps.HorizontalAlign;
            }

            return field;
       }
    }

    [Serializable()]
    public class FieldProperties
    {
        public FieldProperties()
        { }

        public FieldProperties(string name, string formatString, string headerText, HorizontalAlign horizontalAlign)
        {
            Name = name;
            FormatString = formatString;
            HeaderText = headerText;
            HorizontalAlign = horizontalAlign;
        }

        public string Name { get; set; }
        public string FormatString { get; set; }
        public string HeaderText { get; set; }
        public HorizontalAlign HorizontalAlign { get; set; }
    }
}
KristoferA - Huagati.com
+1  A: 

I would use:

var query = from u in db.Users
            select new
            {
                FirstName = u.FirstName,
                LastName = u.LastName,
                FullName = u.FirstName + " " + u.LastName
            };

(from Scott Nichols)

along with a function that reads a Camel Case string and inserts spaces before each new capital (you could add rules for ID etc.). I don't have the code for that function with me for now, but its fairly simple to write.

ck
+1  A: 

I solved my own problem but all of your answers were very helpful and pointed me in the right direction.

In my LINQ query, if a column name had more than one word I would separate the words with an underscore:

Dim query = From u In Users _
            Select First_Name = u.FirstName

Then, within the Paint method of the DataGridView, I replaced all underscores within the header with a space:

Private Sub DataGridView1_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles DataGridView1.Paint
    For Each c As DataGridViewColumn In DataGridView1.Columns
        c.HeaderText = c.HeaderText.Replace("_", " ")
    Next
End Sub
Bryan Roth
+1  A: 

You can also add an event handler to replace those underscores for you!

For those of you who love C#:

datagrid1.ItemDataBound += 
    new DataGridItemEventHandler(datagrid1_HeaderItemDataBound);

And your handler should look like this:

private void datagrid1_HeaderItemDataBound(object sender, DataGridItemEventArgs e)
{

    if (e.Item.ItemType == ListItemType.Header)
    {
        foreach(TableCell cell in e.Item.Cells)
            cell.Text = cell.Text.Replace('_', ' ');
    }

}